﻿using DBLibrary.Model.pojo;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using Control = DBLibrary.Model.pojo.Control;
using User = DBLibrary.Model.pojo.User;

namespace DBLibrary.DataBase
{
    public class Biz: IDisposable
    {
        private DB localDB,remoteDB,DWDB,GBDB ;
        string RealtimeDataKey = ConfigurationHelper.Instance.GetValue("Db:RealTimeDataListKey");
        string PortConfigKey = ConfigurationHelper.Instance.GetValue("Db:PortConfigListKey");
        string SiteConfigKey = ConfigurationHelper.Instance.GetValue("Db:SiteConfigListKey");
        string LogicAlarmListKey = ConfigurationHelper.Instance.GetValue("Db:LogicAlarmListKey");
        string PositionMode = ConfigurationHelper.Instance.GetValue("PositionMode");
        public Biz()
        {
            string connstr = ConfigurationHelper.Instance.GetValue("Db:DbConnStr:local");
            localDB = new DB(connstr); //主连接，本地
            string connstr1 = ConfigurationHelper.Instance.GetValue("Db:DbConnStr:remote");
            if (!string.IsNullOrEmpty(connstr1))
                remoteDB = new DB(connstr1);//辅连接，原系统。系统参数取自于此链接
            else
                remoteDB = localDB;
            string connstrLD = ConfigurationHelper.Instance.GetValue("Db:DbConnStr:dwdb");
            if (!string.IsNullOrEmpty(connstrLD))
                DWDB = new DB(connstrLD);//辅连接，原系统。系统参数取自于此链接
            string connstrBJ = ConfigurationHelper.Instance.GetValue("Db:DbConnStr:gbdb");
            if (!string.IsNullOrEmpty(connstrBJ))
                GBDB = new DB(connstrBJ);//辅连接，原系统。系统参数取自于此链接
        }
        #region 基站数据
        public List<DWStation> GetDWStations()
        {
            string cmdstr;
            if (PositionMode == "2")
            {
                cmdstr = "SELECT [dizhi]      ,[id]       ,[enabled]      ,[name]      ,[address]      ,[createdt]      ,[fztype]      ,a.[areaid]      ,b.[areaname] ,c.areatypename     ,[remark]      ,[ip]  FROM [dbo].[t_fenzhan] a,[dbo].[t_Area] b,[dbo].[t_AreaType] c\r\n";
                cmdstr += " where a.areaid=b.areaid and b.areatypeid=c.areatypeid";
            }
            else
            {
                cmdstr = "SELECT [Id1] as Id\r\n      ,[Id2]\r\n      ,[Enabled]\r\n      ,[Ip]\r\n      ,[Port]\r\n      ,[Name]\r\n      ,[Address]\r\n      ,[CreateTime]\r\n      ,[LastOperate]\r\n      ,[Type]\r\n      ,a.[AreaId]\r\n      ,b.areaName, c.AreaTypeName,[Power]\r\n      ,[Remark]\r\n      ,[MaxDistance]\r\n      ,[PreviousSub]\r\n      ,[NextSub]\r\n  FROM [dbo].[tSubStation] a,[dbo].[tArea] b,[dbo].[tAreaType] c";
                cmdstr += " where a.areaid=b.areaid and b.areatypeid=c.areatypeid";
            }
            List<DWStation> list = DWDB.QueryList<DWStation>(cmdstr);
            return list;
        }
        public List<GBStation> GetGBStations()
        {
            string cmdstr = "SELECT  [systemid]\r\n      ,[enabled]\r\n      ,[id]\r\n      ,[ip]\r\n      ,[port]\r\n      ,[name]\r\n      ,[address]\r\n      ,[createtime]\r\n      ,[type]\r\n      ,[remark]\r\n  FROM [dbo].[t_station]";
            List<GBStation> list = GBDB.QueryList<GBStation>(cmdstr);
            return list;
        }

        #endregion
        #region 人员数据
        public List<PersonLocation> GetPeopleInMine(bool refresh=false,int isDownHole=1)
        {
            if (refresh)
                LocalCache.Remove("CardPersonListKey");
            List<PersonLocation> list;
            bool isDown = isDownHole == 1;
            string cmdstr;
            if (PositionMode == "2")
                cmdstr = "SELECT [rfid] as Card, a.[ID]      ,a.[Enabled]      ,case when a.[name]+'' is null then '未知姓名' when a.[name]='' then '未知姓名'  else a.[Name] end 'Name'     ,[is_xj] as IsDownHole       ,a.fzAdd as StationId,b.[name] as StationName, [Address] as Position         ,[direction]      ,'0' as Distance ,is_bj as IsWarning,is_dy as IsLowVoltage  , [arrivedt] as ArriveTime ,[arrivedt] as UpdateTime,4.0 as Voltage FROM [dbo].[t_rymc] a join [dbo].[t_fenzhan] b on a.fzAdd=b.id order by rfid";
            else
                cmdstr = "SELECT  a.[Card]\r\n      ,a.[Id]\r\n      ,a.[Enabled]\r\n      ,a.[Name]\r\n       ,a.[IsDownHole]\r\n      ,a.[Power]\r\n      ,a.InId1 as StationId, b.[name] as StationName,b.[address] as Position\r\n      ,a.[Direction]\r\n      ,a.[Distance]\r\n  ,a.[IsPersonWarn] as IsWarning\r\n      ,a.[IsPowerWarn] as IsLowVoltage  ,[ArriveTime] as ArriveTime,LastReadTime as UpdateTim,a.Power as Voltage FROM [dbo].[tPerson] a join [dbo].[tSubStation] b on a.InId1=b.Id1  order by card";

            list = DWDB.QueryCacheList<PersonLocation>("CardPersonListKey", cmdstr);
            if(list != null)
                list=list.Where(p=>p.IsDownHole==isDown).ToList();
            return list;
        }
        public List<PersonLocation> GetPeopleByStation(string stationId, string time)
        {
            List<PersonLocation> list;
            string cmdstr;
            if (PositionMode == "2")
                cmdstr = "SELECT [rfid] as Card, a.[ID]      ,a.[Enabled]      ,case when a.[name]+'' is null then '未知姓名' when a.[name]='' then '未知姓名'  else a.[Name] end 'Name'     ,[is_xj] as IsDownHole       ,a.fzAdd as StationId,b.[name] as StationName, [Address] as Position         ,[direction]      ,'0' as Distance ,is_bj as IsWarning,is_dy as IsLowVoltage  , [arrivedt] as ArriveTime ,[arrivedt] as UpdateTime,4.0 as Voltage FROM [dbo].[t_rymc] a join [dbo].[t_fenzhan] b on a.fzAdd=b.id where fzadd=" + stationId + " and arrivedt>='"+ time +"' order by rfid";
            else
                cmdstr = "SELECT  a.[Card]\r\n      ,a.[Id]\r\n      ,a.[Enabled]\r\n      ,a.[Name]\r\n       ,a.[IsDownHole]\r\n      ,a.[Power]\r\n      ,a.InId1 as StationId, b.[name] as StationName,b.[address] as Position\r\n      ,a.[Direction]\r\n      ,a.[Distance]\r\n  ,a.[IsPersonWarn] as IsWarning\r\n      ,a.[IsPowerWarn] as IsLowVoltage  ,[ArriveTime] as ArriveTime,LastReadTime as UpdateTim,a.Power as Voltage FROM [dbo].[tPerson] a join [dbo].[tSubStation] b on a.InId1=b.Id1 where InId1=" + stationId + " and LastReadTime>='"+ time +"'order by card";
            list = DWDB.QueryList<PersonLocation>( cmdstr);
            return list;
        }
        public PersonLocation GetPeopleInMineById(string id)
        {
            PersonLocation p;
            string cmdstr;
            if (PositionMode == "2")
                cmdstr = "SELECT [rfid] as Card, a.[ID]      ,a.[Enabled]      ,case when a.[name]+'' is null then '未知姓名' when a.[name]='' then '未知姓名'  else a.[Name] end 'Name'     ,[is_xj] as IsDownHole       ,a.fzAdd as StationId, b.[name] as StationName,[Address] as Position         ,[direction]      ,'0' as Distance ,is_bj as IsWarning,is_dy as IsLowVoltage  , [arrivedt] as ArriveTime ,[arrivedt] as UpdateTime,4.0 as Voltage FROM [dbo].[t_rymc] a join [dbo].[t_fenzhan] b on a.fzAdd=b.id where rfid=" + id + " order by rfid";
            else
                cmdstr = "SELECT  a.[Card]\r\n      ,a.[Id]\r\n      ,a.[Enabled]\r\n      ,a.[Name]\r\n       ,a.[IsDownHole]\r\n      ,a.[Power]\r\n      ,a.InId1 as StationId,b.[name] as StationName, b.[address] as Position\r\n      ,a.[Direction]\r\n      ,a.[Distance]\r\n  ,a.[IsPersonWarn] as IsWarning\r\n      ,a.[IsPowerWarn] as IsLowVoltage  ,[ArriveTime] as ArriveTime,LastReadTime as UpdateTim ,a.Power as Voltage FROM [dbo].[tPerson] a join [dbo].[tSubStation] b on a.InId1=b.Id1 where card=" + id + " order by card";

            p = DWDB.GetSingle<PersonLocation>(cmdstr);
            return p;
        }
        public List<StationArea> GetStationAreas()
        {
            string cmdstr;
            if (PositionMode == "1")
                cmdstr = "select a.id1 as id,b.areaname  as AreaTypeName from  tSubStation a right join  tArea b on a.AreaId=b.AreaId  order by b.AreaName ";
            else
                cmdstr = "select a.id as id,b.areaname as AreaTypeName from  t_fenzhan a right join  t_Area b on a.AreaId=b.AreaId  order by AreaName ";
            List<StationArea> list=DWDB.QueryList<StationArea>(cmdstr); 
            return list;
        }
        public List<Area> GetAreas()
        {
            string cmdstr;
            if (PositionMode == "1")
                cmdstr = "select areaId ,areaname from  tArea   order by b.AreaName ";
            else
                cmdstr = "select areaId ,areaname from  t_Area  order by AreaName ";
            List<Area> list = DWDB.QueryList<Area>(cmdstr);
            return list;
        }
        #endregion
        #region 分级报警表
        ////
        ///////
        ///分级报警表
        ///////
        //// 
        public List<LevelWarning> GetLevelWarning(string year)
        {
            string cmdstr = "select      系统序号 as sensorId,     报警时间 ad warningTime     端口号 as portId     传感器 as sensor ,安装地点 as location, 分级报警信息 as warningMessage ,分级类型 as level from 分级报警表" + year;
            List<LevelWarning> list = localDB.QueryList<LevelWarning>(cmdstr);
            return list;
        }
        public void CreateLevelWarnTable(string year)
        {
            string cmdstr = "IF OBJECT_ID('分级报警表" + year + "','u') IS NULL BEGIN CREATE TABLE [dbo].[分级报警表" + year + "]([系统序号] [int] NULL,\r\n\t[报警时间] [datetime] NULL,\r\n\t[端口号] [nvarchar](10) NULL,\r\n\t[传感器] [nvarchar](50) NULL,\r\n\t[安装地点] [nvarchar](100) NULL,\r\n\t[分级报警信息] [ntext] NULL,\r\n\t[分级类型] [smallint] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] SELECT 1 END ELSE SELECT 0";
            localDB.SqDDLlExecute(cmdstr);
        }
        public int AddLevelWarning(LevelWarning warning, string year)
        {
            int i = 0;
            // string cmdstr = "INSERT INTO [dbo].[分级报警"+year+"]    ([系统序号],[报警时间],[端口号],[传感器],[安装地点],[分级报警信息] ,[分级类型])  VALUES  (<系统序号, int,>       ,<报警时间, datetime,>      ,<端口号, nvarchar(10),>         ,<传感器, nvarchar(50),>          ,<安装地点, nvarchar(100),>      ,<分级报警信息, ntext,>      ,<分级类型, smallint,>)";
            if (!IsExistTable("分级报警" + year))
                CreateLevelWarnTable(year);
            string cmdstr = "INSERT INTO[dbo].[分级报警" + year + "]   ([系统序号],[报警时间],[端口号],[传感器],[安装地点],[分级报警信息],[分级类型]) VALUES (" + warning.SensorId + ", '" + warning.WarningTime + "','" + warning.PortId + "','" + warning.Sensor + "','" + warning.Location + "','" + warning.WarningMessage + "','" + warning.Level + "')";
            i = localDB.SqlExecute(cmdstr);
            return i;
        }
        #endregion
        #region 用户数据
        /// <summary>
        /// 获取用户表
        /// </summary>
        /// <returns></returns>
        public List<Model.pojo.User> GetUserList()
        {
            string cmdstr = "SELECT [ID] as UserId      ,[分组] as UserGroup      ,[用户名] UserName      ,[密码] UserPwd      ,[显示名称] as UserRealName     ,[UserUnit]\r\n      ,[权限字串] as UserStatus  FROM [dbo].[用户权限] order by ID DESC";
            List<User> list = localDB.QueryList<User>(cmdstr);
            return list;
        }
        public int AddUser(User user)
        {
            int i;
         
            var list=GetUserList();
            if (list == null||list.Count==0)
                i = 1;
            else 
                i=list.First().UserId+1;
            if (list!=null && list.Where(p => p.UserName == user.UserName).Count() > 0)
                return -1;
            string cmdstr = "INSERT INTO 用户权限 ( [ID]\r\n      ,[分组]\r\n      ,[用户名]\r\n      ,[密码]\r\n      ,[显示名称]\r\n      ,[UserUnit]\r\n      ,[权限字串]) VALUES ("+i+",1,'"+user.UserName+"','"+Md5Hash(user.UserPwd)+"','"+user.UserRealName+"','"+user.UserUnit+"','"+user.UserStatus+"') ";
            localDB.SqlExecute(cmdstr);
            
            return (int)i;
        }
        public int UpdateUser(User user)
        {
            int i;
            string cmdstr;
            if(user.UserPwd=="******")
                cmdstr = "update 用户权限  set [分组] ="+user.UserGroup+"    ,[用户名]='"+user.UserName+"'   ,[显示名称] ='"+ user.UserRealName+"'     ,[UserUnit]='"+user.UserUnit+"', 权限字串='"+ user.UserStatus+"' where id="+user.UserId;
            else
                cmdstr = "update 用户权限  set [分组] =" + user.UserGroup + "    ,[用户名]='" + user.UserName + "'   ,[密码] ='" + Md5Hash(user.UserPwd) + "'  ,[显示名称] ='" + user.UserRealName + "'     ,[UserUnit]='" + user.UserUnit + "', 权限字串='" + user.UserStatus + "' where id=" + user.UserId;
            i = localDB.SqlExecute(cmdstr);
            return (int)i;
        }
        public int DeleteUser(User user)
        {
            int i;
            string cmdstr;
            cmdstr = "delete from  用户权限   where id=" + user.UserId;
            i = localDB.SqlExecute(cmdstr);
            return (int)i;
        }

        public User UserLogin(User user)
        {

            string cmdstr = "SELECT [ID] as UserId      ,[分组] as UserGroup      ,[用户名] UserName      ,[密码] UserPwd      ,[显示名称] as UserRealName     ,[UserUnit]\r\n      ,[权限字串] as UserStatus  FROM [dbo].[用户权限] where 用户名='"+user.UserName+"' and 密码='"+Md5Hash(user.UserPwd)+"' and 权限字串='1'";
            var u=localDB.GetSingle<User>(cmdstr);
            return u;
        }
        #endregion 用户数据
        #region 工作面类型
        public List<WorkFaceType> GetWorkFaceList()
        {
            string str = "select 工作面类型编码 as Code,名称 as Name from 工作面类型";
            List<WorkFaceType> list = localDB.QueryList<WorkFaceType>(str);
            return list;
        }
        #endregion
        /// <summary>
        /// 获取即时数据表   (查、插入)
        /// </summary>
        /// <returns></returns>
        #region 即时数据表
        public void SyncImmediate()
        {
            string str = "delete  from 即时数据表";
            localDB.SqlExecute(str);
            List<Immediate> data = GetImmediateList(true);
            foreach (Immediate item in data)
            {
                if (item.IsWarning == 1)
                    item.Status = 1;

                AddImmediate(item);
            }
            LocalCache.Remove(RealtimeDataKey);
        }
        public List<Immediate> GetImmediateList(bool FromRemote=false)
        {
            string cmdstr;
            if(FromRemote==false)
                cmdstr = "select cast(b.分站地址 as varchar)  + '-' + cast(b.端口序号 as varchar)   as CustomName,b.分站地址 as BrachAdressId, b.系统序号 as SensorId,b.传感器名称 as SensorName,b.传感器类型 as SensorType,a.有效否 as isValid, a.当前值 as CurrentValue, a.当前值 as ShowRealValue,a.最大值 as MaxValue,a.最小值 as MinValue,a.平均值 as AverageValue, a.报警否 as IsWarning, a.报警信息 as WarningMessage, case c.运行状态 \r\nwhen '断线' then 11\r\nelse a.传感器状态 \r\nend as status,  a.时间 as dateTime, a.报警开始时间 as AlarmStartTime, upstate as UpState, 断电否 as IsOutage,断线否 as IsOffline, 故障否 as IsError, 馈电异常否 as IsFeedError,断电开始时间 as OutageStartTime, 断线开始时间 as OfflineStartTime,故障开始时间 as ErrorStartTime, 馈电异常开始时间 as FeedErrorStartTime,最大值时刻 as MaxValueTime, b.开显示 as OpenLabel,b.停显示  as StopLabel, b.安装地点 as InstallPlace, b.单位 as Unit from 即时数据表 a  join 端口参数表 b on a.系统序号=b.系统序号 join 站点配置表 c on a.分站地址=c.分站地址 where c.有效否=1 and a.有效否=1 and b.是否已删除=0 and 是否有效=1  order by b.分站地址, b.端口序号\r\n\r\n";
            else
                cmdstr = "select cast(b.分站地址 as varchar)  + '-' + cast(b.端口序号 as varchar)   as CustomName,b.分站地址 as BrachAdressId, b.系统序号 as SensorId,b.传感器名称 as SensorName,b.传感器类型 as SensorType,a.有效否 as isValid, a.当前值 as CurrentValue,a.当前值 as ShowRealValue,a.最大值 as MaxValue,a.最小值 as MinValue,a.平均值 as AverageValue, a.报警否 as IsWarning, a.报警信息 as WarningMessage,  case c.运行状态 \r\nwhen '断线' then 11\r\nelse a.传感器状态 \r\nend as status, a.时间 as dateTime, a.报警开始时间 as AlarmStartTime, upstate as UpState, 断电否 as IsOutage,断线否 as IsOffline, 故障否 as IsError, 馈电异常否 as IsFeedError,断电开始时间 as OutageStartTime, 断线开始时间 as OfflineStartTime,故障开始时间 as ErrorStartTime, 馈电异常开始时间 as FeedErrorStartTime,最大值时刻 as MaxValueTime, b.开显示 as OpenLabel,b.停显示  as StopLabel, b.安装地点 as InstallPlace, b.单位 as Unit from 即时数据表 a  join 端口参数表 b on a.系统序号=b.系统序号 join 站点配置表 c on a.分站地址=c.分站地址 where c.有效否=1 and a.有效否=1 and b.是否已删除=0 and 是否有效=1  order by b.分站地址, b.端口序号\r\n\r\n";

            //cmdstr += "on a.系统序号=b.系统序号 where b.是否已删除=0 and 是否有效=1  order by b.分站地址, b.端口序号";
            List<Immediate> list;
            if (FromRemote == false)
                list = localDB.QueryCacheList<Immediate>(RealtimeDataKey, cmdstr);
            else
                list = remoteDB.QueryList<Immediate>(cmdstr);
            return list;
        }

        public void InitImmediateTable()
        {

            LocalCache.Remove(RealtimeDataKey);
            List<PortPara> portParas = GetPortParaList(false, true);
            List<SiteConfig> siteConfigList = GetSiteConfigList(false);

            if (remoteDB != null && remoteDB!=localDB)
            {
                SyncSiteConfig(siteConfigList);
                SyncPortPara(portParas);
                SyncImmediate();
            }
            List<Immediate> data = GetImmediateList();
            if (portParas == null || portParas.Count == 0)
                return;
            List<int> immediates = data.Select(p=>p.SensorId).ToList();
            string cmdstr = "";
            foreach (var v in portParas)
            {
                if (!immediates.Contains(v.SensorId) && v.IsValid==1 && v.IsDelete==0)
                {
                    cmdstr += "insert into 即时数据表 (   系统序号   , 分站地址 \r\n      , 有效否 \r\n      , 当前值 \r\n      , 最大值 \r\n      , 最小值 \r\n      , 平均值 \r\n      , 报警否 \r\n      , 断电否 \r\n      , 断线否 \r\n      , 故障否 \r\n      , 馈电异常否 \r\n      , 报警信息 \r\n      , 传感器状态 \r\n      , 时间 \r\n      , 当月累计值 \r\n      , 当月累计次数 \r\n       , 上传否 \r\n      , 上传否2 \r\n      , UpState \r\n      , 标校时间 \r\n      , 分级报警级别 \r\n      ,  报警开始时间 \r\n      , 断电开始时间 \r\n      , 断线开始时间 \r\n      , 故障开始时间 \r\n      , 馈电异常开始时间 ,最大值时刻)";
                    cmdstr += " values('" + v.SensorId + "','" + v.BranchAddr + "','" + v.IsValid + "',0,0,0,0,0,0,0,0,0,'',20,null,0,0,null,null,null,null,0,null,null,null,null,null,null);\n";
                }
            }
            foreach (var v in immediates)
            {
                if (portParas.Find(s => s.SensorId == v) == null)
                {
                    cmdstr += "delete from 即时数据表 where 系统序号="+v.ToString()+";";
                }
            }
            localDB.SqlExecute(cmdstr);
            cmdstr = "update 站点配置表 set 判定断线时间=5 where 判定断线时间=0 or 判定断线时间=null; ";
            localDB.SqlExecute(cmdstr);
            LocalCache.Remove(RealtimeDataKey);
        }
        public void ClearImmediateValue(int SensorId=0)
        {
            string cmdStr = "update 即时数据表 set 最大值=当前值,最小值=当前值,平均值=当前值 ";
            if (SensorId != 0)
                cmdStr += " where 系统序号=" + SensorId;
            localDB.SqlExecute(cmdStr);
        }

        public int DeleteImmediateByID(string SensorId)
        {
            string cmdStr = " delete from [即时数据表] where 系统序号 = " + SensorId;
            int i = localDB.SqlExecute(cmdStr);
            LocalCache.Remove(RealtimeDataKey);
            return i;
        }
        //+ "','" + immediate.MaxValue
        public int AddImmediate(Immediate immediate)
        {
            string cmdstr = "insert into 即时数据表 (   系统序号   , 分站地址 \r\n      , 有效否 \r\n      , 当前值 \r\n      , 最大值 \r\n      , 最小值 \r\n      , 平均值 \r\n      , 报警否 \r\n      , 断电否 \r\n      , 断线否 \r\n      , 故障否 \r\n      , 馈电异常否 \r\n      , 报警信息 \r\n      , 传感器状态 \r\n      , 时间 \r\n      , 当月累计值 \r\n      , 当月累计次数 \r\n       , 上传否 \r\n      , 上传否2 \r\n      , UpState \r\n      , 标校时间 \r\n      , 分级报警级别 \r\n      , 安全措施 \r\n      , 报警开始时间 \r\n      , 断电开始时间 \r\n      , 断线开始时间 \r\n      , 故障开始时间 \r\n      , 馈电异常开始时间 )";
            cmdstr += " values('" + immediate.SensorId + "','" + immediate.BrachAdressId + "','" + immediate.IsValid + "','" + immediate.CurrentValue + "','" + immediate.MaxValue + "','" + immediate.MinValue + "','" + immediate.AverageValue + "','" + immediate.IsWarning + "','" + immediate.IsOutage + "','" + immediate.IsOffline + "','" + immediate.IsError + "','" + immediate.IsFeedError + "','" + immediate.WarningMessage + "','" + immediate.Status + "',";
            if (immediate.DateTime.ToString("yyyy-MM-dd HH:mm:ss") != "0001-01-01 00:00:00")
                cmdstr+="'"+immediate.DateTime + "','";
            else
                cmdstr +=  @"null,'";
            cmdstr+=immediate.MonthValue + "','" + immediate.MonthCount +  "','" + immediate.IsUpload + "','" + immediate.IsUpload2 + "','" + immediate.UpState + "',null,'" + immediate.AlarmLevel + "','" + immediate.SafeGuards + "',null,null,null,null,null)";
            int i = localDB.SqlExecute(cmdstr);
            LocalCache.Remove(RealtimeDataKey);
            return i;
        }
        /// <summary>
        /// 查询开始报警但没有消除的实时数据
        /// </summary>
        /// <param name="sensorId"></param>
        /// <returns></returns>
        public Immediate GetImmediate(int sensorId)
        {
            string cmdstr = "select cast(b.分站地址 as varchar)  + '-' + cast(b.端口序号 as varchar)   as CustomName,b.分站地址 as BrachAdressId, b.系统序号 as SensorId,b.传感器名称 as SensorName,b.传感器类型 as SensorType,a.有效否 as isValid, a.当前值 as CurrentValue, a.当前值 as ShowRealValue,a.最大值 as MaxValue,a.最小值 as MinValue,a.平均值 as AverageValue, a.报警否 as IsWarning, a.报警信息 as WarningMessage, case c.运行状态 \r\nwhen '断线' then 11\r\nelse a.传感器状态 \r\nend as status,  a.时间 as dateTime, a.报警开始时间 as AlarmStartTime, upstate as UpState, 断电否 as IsOutage,断线否 as IsOffline, 故障否 as IsError, 馈电异常否 as IsFeedError,断电开始时间 as OutageStartTime, 断线开始时间 as OfflineStartTime,故障开始时间 as ErrorStartTime, 馈电异常开始时间 as FeedErrorStartTime, 最大值时刻 as MaxValueTime, b.开显示 as OpenLabel,b.停显示  as StopLabel, b.安装地点 as InstallPlace, b.单位 as Unit from 即时数据表 a  join 端口参数表 b on a.系统序号=b.系统序号 join 站点配置表 c on a.分站地址=c.分站地址 where c.有效否=1 and a.有效否=1 and b.是否已删除=0 and 是否有效=1 and a.系统序号=" + sensorId;
            Immediate immediate = localDB.GetSingle<Immediate>(cmdstr);
            return immediate;
        }
        public void UpdateImmediate(Immediate immediateData)
        {
            string cmd = "update 即时数据表 WITH (TABLOCKX) set 报警否=" + immediateData.IsWarning.ToString();
            cmd += ",断电否=" + immediateData.IsOutage.ToString();
            cmd += ",断线否=" + immediateData.IsOffline.ToString();
            cmd += ",故障否=" + immediateData.IsError.ToString();
            cmd += ",馈电异常否=" + immediateData.IsFeedError.ToString();
            cmd += ",当前值=" + immediateData.CurrentValue.ToString();
            cmd += ",最小值=" + immediateData.MinValue.ToString();
            cmd += ",最大值=" + immediateData.MaxValue.ToString();
            cmd += ",平均值=" + immediateData.AverageValue.ToString();
            cmd += ",报警信息='" + immediateData.WarningMessage + "'";
            cmd += ",传感器状态=" + immediateData.Status.ToString();
            if(immediateData.AlarmStartTime.ToString("yyyy-MM-dd HH:mm:ss")!= "0001-01-01 00:00:00")
                cmd += ",报警开始时间='" + immediateData.AlarmStartTime + "'";
            else
                cmd += ",报警开始时间=" + @"null";
            if (immediateData.OutageStartTime.ToString("yyyy-MM-dd HH:mm:ss") != "0001-01-01 00:00:00")
                cmd += ",断电开始时间='" + immediateData.OutageStartTime + "'";
            else
                cmd += ",断电开始时间=" + @"null";
            if (immediateData.OfflineStartTime.ToString("yyyy-MM-dd HH:mm:ss") != "0001-01-01 00:00:00")
                cmd += ",断线开始时间='" + immediateData.OfflineStartTime + "'";
            else
                cmd += ",断线开始时间=" + @"null";
            if (immediateData.ErrorStartTime.ToString("yyyy-MM-dd HH:mm:ss") != "0001-01-01 00:00:00")
                cmd += ",故障开始时间='" + immediateData.ErrorStartTime + "'";
            else
                cmd += ",故障开始时间=" + @"null";
            if (immediateData.FeedErrorStartTime.ToString("yyyy-MM-dd HH:mm:ss") != "0001-01-01 00:00:00")
                cmd += ",馈电异常开始时间='" + immediateData.FeedErrorStartTime + "'";
            else
                cmd += ",馈电异常开始时间=" + @"null";
            if (immediateData.MaxValueTime.ToString("yyyy-MM-dd HH:mm:ss") != "0001-01-01 00:00:00")
                cmd += ",最大值时刻='" + immediateData.MaxValueTime + "'";
            else
                cmd += ",最大值时刻=" + @"null";
            cmd += ",时间='" + immediateData.DateTime + "'";
            cmd += ",upstate="+ immediateData.UpState.ToString()+"";
            cmd += " where 系统序号=" + immediateData.SensorId.ToString();
            localDB.SqlExecute(cmd);
        }
        #endregion
        #region 传感器字典
        public int AddSensorDict(SensorDict dict)
        {
            string cmdstr = "INSERT INTO [dbo].[传感器字典]\r\n ([传感器类型],\r\n[传感器代码]\r\n,[是否模拟量]\r\n,[传感器名称]\r\n,[单位]\r\n,[量程上限]\r\n,[量程下限]\r\n,[计算方法]\r\n,[是否负数]\r\n,[小数位数],基值,系数,开显示,停显示)\r\n     VALUES\r\n('"+dict.SensorType+"',"+dict.SensorCode+","+dict.IsAnalog+",'"+dict.SensorName+"','"+dict.Unit+"',"+dict.HighLimit+","+dict.LowLimit+","+dict.ComputeMethod+","+dict.IsNegative+" ,"+dict.Digitals+","+dict.BaseValue+","+dict.Cofficent+",'"+dict.OpenLabel+"','"+dict.StopLabel+"');SELECT SCOPE_IDENTITY() AS NewUserId";
            var u = localDB.SqlExecuteScalar(cmdstr);
            return u;
        }
        public int UpdateSensorDict(SensorDict dict)
        {
            string cmdstr = "UPDATE [dbo].[传感器字典]";
            cmdstr += "SET[传感器类型] = '" + dict.SensorType+"'";
            cmdstr += ",[传感器代码] = " + dict.SensorCode;
            cmdstr += ",[是否模拟量] = " + dict.IsAnalog;
            cmdstr += ",[传感器名称] = '" + dict.SensorName + "'";
            cmdstr += " ,[单位] = '" + dict.Unit + "'";
            cmdstr += ",[量程上限] = " + dict.HighLimit;
            cmdstr += ",[量程下限] = " + dict.LowLimit;
            cmdstr += ",[计算方法] = " + dict.ComputeMethod;
            cmdstr += ",[是否负数] = " + dict.IsNegative;
            cmdstr += ",[小数位数] = "+dict.Digitals;
            cmdstr += ",[基值] = " + dict.BaseValue;
            cmdstr += ",[系数] = " + dict.Cofficent;
            cmdstr += ",[开显示] = '" + dict.OpenLabel+"'";
            cmdstr += ",[停显示] = '" + dict.StopLabel+"'";
            cmdstr += " where id=" + dict.Id;
            var u = localDB.SqlExecute(cmdstr);
            return u;
        }
        public List<SensorDict> GetSensorDictList(int isAnalog=1)
        {
            string cmdstr = "SELECT [id] as Id\r\n,[传感器类型] as SensorType\r\n,[传感器代码] as SensorCode\r\n,[是否模拟量] as IsAnalog\r\n,[传感器名称] as SensorName\r\n,[单位] as Unit\r\n,[量程上限] As HighLimit\r\n,[量程下限] as LowLimit\r\n,[计算方法] as ComputeMethod\r\n,[是否负数] as IsNegative\r\n,[小数位数] as Digitals\r\n ,基值 as BaseValue,系数 as Cofficent,开显示 as OpenLabel,停显示 as StopLabel FROM [dbo].[传感器字典]";
            List<SensorDict> list=localDB.QueryList<SensorDict>(cmdstr);
            return list;
        }
        public SensorDict GetSensorDictByCode(int code)
        {
            string cmdstr = "SELECT [id] as Id\r\n,[传感器类型] as SensorType\r\n,[传感器代码] as SensorCode\r\n,[是否模拟量] as IsAnalog\r\n,[传感器名称] as SensorName\r\n,[单位] as Unit\r\n,[量程上限] As HighLimit\r\n,[量程下限] as LowLimit\r\n,[计算方法] as ComputeMethod\r\n,[是否负数] as IsNegative\r\n,[小数位数] as Digitals\r\n ,基值 as BaseValue,系数 as Cofficent,开显示 as OpenLabel,停显示 as StopLabel FROM [dbo].[传感器字典] where 传感器代码=" + code;
            SensorDict dict = localDB.GetSingle<SensorDict>(cmdstr);
            return dict;
        }
        public int DeleteSensorDict(int id)
        {
            string cmdstr = "delete from 传感器字典 where id="+id;
            int i=localDB.SqlExecute(cmdstr);

            return i;
        }
        #endregion
        /// <summary>
        /// 获取报警数据表   (查、插入)
        /// </summary>
        /// <returns></returns>
        #region 报警数据表
        public List<AlarmData> GetAlarmDataList(int? sensorID,DateTime StartDate,DateTime EndDate)
        {
            DateTime date=StartDate;
            string cmdstr="";
            for (int i = 0; date <= EndDate; i++)
            {
                if (IsExistTable("报警数据表" + date.AddDays(i).ToString("yyyyMMdd").Substring(2)))
                {
                    date = date.AddDays(i);
                    break;
                }
            }
            if(date>EndDate)
                return null;
            cmdstr = "select  cast(端口参数表.分站地址 as nvarchar(3))+'-'+ cast(端口参数表.端口序号 as nvarchar(3)) as DisplayName,端口参数表.系统序号 as SensorId, 报警值 as AlarmValue,当前值 as CurrentValue,时间 as Time,报警信息 as AlarmMsg,传感器状态 as Status ,统计值 as CountValue,cast(备注 as nvarchar(100)) as Describe,原因 as Reason, 措施 AS Guards  from 报警数据表" + date.ToString("yyyyMMdd").Substring(2)+",端口参数表 where 报警数据表" + date.ToString("yyyyMMdd").Substring(2)+".系统序号=端口参数表.系统序号 and 报警数据表" + date.ToString("yyyyMMdd").Substring(2)+ ".传感器状态>-200";

            for (int i = 1; date.AddDays(i) <= EndDate; i++)
            {
                if (IsExistTable("报警数据表" + date.AddDays(i).ToString("yyyyMMdd").Substring(2)))
                    cmdstr += " union select  cast(端口参数表.分站地址 as nvarchar(3))+'-'+ cast(端口参数表.端口序号 as nvarchar(3)) as DisplayName,端口参数表.系统序号 as SensorId, 报警值 as AlarmValue,当前值 as CurrentValue,时间 as Time,报警信息 as AlarmMsg,传感器状态 as Status ,统计值 as CountValue,cast(备注 as nvarchar(100)) as Describe ,原因 as Reason, 措施 AS Guards from 报警数据表" + date.AddDays(i).ToString("yyyyMMdd").Substring(2) + ",端口参数表 where 报警数据表" + date.AddDays(i).ToString("yyyyMMdd").Substring(2) + ".系统序号=端口参数表.系统序号 and 报警数据表" + date.AddDays(i).ToString("yyyyMMdd").Substring(2) + ".传感器状态>-200";
            };
            if(sensorID!=null)
                cmdstr += " and 端口参数表.系统序号=" + sensorID + " ";
            if (StartDate != null)
                cmdstr += " and 时间>='" + StartDate + "' ";
            if (EndDate != null)
                cmdstr += " and 时间<='" + EndDate + "' ";
            cmdstr += " order by 时间 DESC";
            List<AlarmData> list = localDB.QueryList<AlarmData>(cmdstr);

            return list;
        }
        public List<AlarmData> GetTodayAlarmData( DateTime date)
        {
            if (!IsExistTable("报警数据表" + date.ToString("yyMMdd")))
                CreateAlertTable(date.ToString("yyMMdd"));
            string cmdstr = "select  系统序号 as SensorId,报警值 as AlarmValue,当前值 as CurrentValue,时间 as Time,报警信息 as AlarmMsg,传感器状态 as Status ,统计值 as CountValue,备注 as Describe,措施 as Guards,原因 as Reason ,校准浓度 as StandardizeSolubility,上传否 as IsUpload,上传否2 as IsUpload2,上传否3 as IsUpload3,上传否4 as IsUpload4   from 报警数据表" + date.ToString("yyMMdd");
            cmdstr += " where 传感器状态 in (-1, -3,-104)" ;
            List<AlarmData> list = localDB.QueryList<AlarmData>(cmdstr);
            return list;
        }
        //wang 
        public AlarmData GetAlarmDataById(string Id, DateTime? date, int Status)
        {
            string cmdstr = "select  系统序号 as SensorId,报警值 as AlarmValue,当前值 as CurrentValue,时间 as Time,报警信息 as AlarmMsg,传感器状态 as Status ,统计值 as CountValue,备注 as Describe,措施 as Guards,原因 as Reason ,校准浓度 as StandardizeSolubility,上传否 as IsUpload,上传否2 as IsUpload2,上传否3 as IsUpload3,上传否4 as IsUpload4   from 报警数据表" + ((DateTime)date).ToString("yyMMdd");
            cmdstr += " where 系统序号=" + Id;
            cmdstr += " and 传感器状态=" + Status + "";
            if (date != null)
            {
                cmdstr += "  and CONVERT(varchar(100), 时间, 120 )='" + ((DateTime)date).ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }

            AlarmData AlarmData = localDB.GetSingle<AlarmData>(cmdstr);
            return AlarmData;
        }
        public int UpdateAlarmReason(AlarmData alarm)
        {
            string tableDate = alarm.Time.ToString("yyMMdd");
            string cmdstr = "update 报警数据表" + tableDate + " set 原因='"+alarm.Reason+"',措施='"+alarm.Guards+"' where 系统序号="+alarm.SensorId.ToString()+ " and CONVERT(varchar(100), 时间, 120 )='"+alarm.Time.ToString("yyyy-MM-dd HH:mm:ss")+"' and 传感器状态="+alarm.Status;
            //string cmdstr = "insert into 报警数据表" + tableDate + " values('" + alarmData.SensorId + "','" + alarmData.AlarmValue + "','" + alarmData.CurrentValue + "','" + alarmData.Time + "','" + alarmData.AlarmMsg + "','" + alarmData.Status + "','" + alarmData.CountValue + "','" + alarmData.Describe + "','" + alarmData.IsUpload + "','" + alarmData.IsUpload2 + "','" + alarmData.IsUpload3 + "','" + alarmData.IsUpload4 + "','" + alarmData.Guards + "','" + alarmData.Reason + "','" + alarmData.HotBack1 + "','" + alarmData.HotBack2 + "','" + alarmData.StandardizeSolubility + "')";
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }
        //获取传感器最后报警记录，zhao 
        public AlarmData GetLastAlarmDataById(string Id, DateTime date)
        {
            string cmdstr = "select  系统序号 as SensorId,报警值 as AlarmValue,当前值 as CurrentValue,时间 as Time,报警信息 as AlarmMsg,传感器状态 as Status ,统计值 as CountValue,备注 as Describe,措施 as Guards,原因 as Reason ,校准浓度 as StandardizeSolubility,上传否 as IsUpload,上传否2 as IsUpload2,上传否3 as IsUpload3,上传否4 as IsUpload4   from 报警数据表" + date.ToString("yyyyMMdd").Substring(2);
            cmdstr += " where 系统序号=" + Id + " and (传感器状态=-104 or 传感器状态=-4) order by 时间 DESC";
            List<AlarmData> list = localDB.QueryList<AlarmData>(cmdstr);
            if (list == null || list.Count < 1)
            {
                cmdstr += "  select  系统序号 as SensorId,报警值 as AlarmValue,当前值 as CurrentValue,时间 as Time,报警信息 as AlarmMsg,传感器状态 as Status ,统计值 as CountValue,备注 as Describe,措施 as Guards,原因 as Reason ,校准浓度 as StandardizeSolubility,上传否 as IsUpload,上传否2 as IsUpload2,上传否3 as IsUpload3,上传否4 as IsUpload4   from 报警数据表" + date.AddDays(-1).ToString("yyyyMMdd").Substring(2);
                cmdstr += " where 系统序号=" + Id + " and (传感器状态=-104 or 传感器状态=-4) order by 时间 DESC";
                list = localDB.QueryList<AlarmData>(cmdstr);
            }


            AlarmData AlarmData =null;
            if (list != null && list.Count > 0)
                AlarmData = list[0];
            return AlarmData;
        }

        public int AddAlarmData(AlarmData alarmData, string tableDate)
        {
            if (!IsExistTable("报警数据表" + tableDate))
                CreateAlertTable(tableDate);
            string cmdstr = "insert into 报警数据表" + tableDate + " (系统序号,报警值,当前值,时间,报警信息,传感器状态,统计值,备注,上传否,上传否2,上传否3,上传否4,措施,原因,校准浓度) values ('" + alarmData.SensorId + "','" + alarmData.AlarmValue + "','" + alarmData.CurrentValue + "','" + alarmData.Time.ToString("yyyy-MM-dd HH:mm:ss.fff") + "','" + alarmData.AlarmMsg + "','" + alarmData.Status + "','" + alarmData.CountValue + "','" + alarmData.Describe + "','" + alarmData.IsUpload + "','" + alarmData.IsUpload2 + "','" + alarmData.IsUpload3 + "','" + alarmData.IsUpload4 + "','" + alarmData.Guards + "','" + alarmData.Reason + "','" + alarmData.StandardizeSolubility + "')"; 
            //string cmdstr = "insert into 报警数据表" + tableDate + " values('" + alarmData.SensorId + "','" + alarmData.AlarmValue + "','" + alarmData.CurrentValue + "','" + alarmData.Time + "','" + alarmData.AlarmMsg + "','" + alarmData.Status + "','" + alarmData.CountValue + "','" + alarmData.Describe + "','" + alarmData.IsUpload + "','" + alarmData.IsUpload2 + "','" + alarmData.IsUpload3 + "','" + alarmData.IsUpload4 + "','" + alarmData.Guards + "','" + alarmData.Reason + "','" + alarmData.HotBack1 + "','" + alarmData.HotBack2 + "','" + alarmData.StandardizeSolubility + "')";
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }
        public void CreateAlertTable(string yymmdd)
        {
            string cmdstr = "IF OBJECT_ID('报警数据表" + yymmdd + "','u') IS NULL BEGIN CREATE TABLE [dbo].[报警数据表" + yymmdd + "]( [系统序号] [int] NOT NULL, [报警值] [float] NULL, [当前值] [float] NULL, [时间] [datetime] NULL, [报警信息] [nvarchar](120) NULL, [传感器状态] [smallint] NULL, [统计值] [int] NULL, [备注] [ntext] NULL, [上传否] [bit] NULL, [上传否2] [bit] NULL, [上传否3] [bit] NULL, [上传否4] [bit] NULL, [措施] [nvarchar](255) NULL, [原因] [nvarchar](255) NULL,[热备状态1] [bit] NULL,[热备状态2] [bit] NULL, [校准浓度] [float] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] SELECT 1 END ELSE SELECT 0";
            localDB.SqDDLlExecute(cmdstr);
        }
        public bool IsExistTable(string tableName)
        {
            string sqlStr = "IF OBJECT_ID('" + tableName + "','u') IS NULL SELECT 0 ELSE SELECT 1";
            string res = localDB.SqDDLlExecute(sqlStr);
            if (res.Equals("1"))
                return true;
            else
                return false;

        }

        public void UpdateAlarmData(AlarmData alarmData, string Id, string tableDate, DateTime? time, string AlarmMsg)
        {
            string cmd = "update 报警数据表" + tableDate + "  set 报警值=" + alarmData.AlarmValue.ToString();
            cmd += ",系统序号=" + alarmData.SensorId.ToString();
            cmd += ",当前值=" + alarmData.CurrentValue.ToString();
            cmd += ",时间='" + alarmData.Time + "'";
            cmd += ",报警信息='" + alarmData.AlarmMsg.ToString() + "'";
            cmd += ",传感器状态=" + alarmData.Status.ToString();
            cmd += ",统计值='" + alarmData.CountValue + "'";
            cmd += ",备注='" + alarmData.Describe + "'";
            cmd += ",措施='" + alarmData.Guards + "'";
            cmd += ",原因='" + alarmData.Reason + "'";
            cmd += ",校准浓度='" + alarmData.StandardizeSolubility + "'";
            cmd += ",上传否=" + alarmData.IsUpload;
            cmd += ",上传否2=" + alarmData.IsUpload2;
            cmd += ",上传否3=" + alarmData.IsUpload3;
            cmd += ",上传否4=" + alarmData.IsUpload4;
            cmd += ",热备状态1='" + alarmData.HotBack1 + "'";
            cmd += ",热备状态2='" + alarmData.HotBack2 + "'";
            cmd += " where 系统序号=" + Id;
            cmd += " and 时间='" + time + "'";
            cmd += " and 报警信息='" + AlarmMsg + "'";
            localDB.SqlExecute(cmd);
        }
        #endregion

        #region 分级报警数据表
        public List<AlarmLevelData> GetAlarmLevelDataList(string tableDate)
        {
            string cmdstr = "select  系统序号 as SensorId,报警值 as AlarmValue,当前值 as CurrentValue,时间 as Time,报警信息 as AlarmMsg,传感器状态 as Status ,统计值 as CountValue,备注 as Describe,措施 as Guards,原因 as Reason ,校准浓度 as StandardizeSolubility, 热备状态1 as HotBack1,热备状态2 as HotBack2  from 分级报警数据表" + tableDate;
            cmdstr += " order by 系统序号";
            List<AlarmLevelData> list = localDB.QueryList<AlarmLevelData>(cmdstr);

            if (list == null)
            {
                cmdstr = "select  系统序号 as SensorId,报警值 as AlarmValue,当前值 as CurrentValue,时间 as Time,报警信息 as AlarmMsg,传感器状态 as Status ,统计值 as CountValue,备注 as Describe,措施 as Guards,原因 as Reason ,校准浓度 as StandardizeSolubility from 分级报警数据表" + tableDate;
                cmdstr += " order by 系统序号";
                list = localDB.QueryList<AlarmLevelData>(cmdstr);
            }
            return list;
        }
        //wang 
        public AlarmLevelData GetAlarmLevelDataById(string Id, string tableDate, DateTime? date, string AlarmMsg)
        {
            string cmdstr = "select  系统序号 as SensorId,报警值 as AlarmValue,当前值 as CurrentValue,时间 as Time,报警信息 as AlarmMsg,传感器状态 as Status ,统计值 as CountValue,备注 as Describe,措施 as Guards,原因 as Reason ,校准浓度 as StandardizeSolubility,上传否 as IsUpload,上传否2 as IsUpload2,上传否3 as IsUpload3,上传否4 as IsUpload4   from 分级报警数据表" + tableDate;
            cmdstr += " where 系统序号=" + Id;
            cmdstr += " and 报警信息='" + AlarmMsg + "'";
            if (date != null)
            {
                cmdstr += " and 时间='" + date + "'";
            }

            AlarmLevelData AlarmData = localDB.GetSingle<AlarmLevelData>(cmdstr);
            return AlarmData;
        }


        public int AddAlarmLevelData(AlarmLevelData alarmData, string tableDate)
        {
            if (!IsExistTable("分级报警数据表" + tableDate))
                CreateAlertLevelTable(tableDate);
            string cmdstr = "insert into 分级报警数据表" + tableDate + " (系统序号,报警值,当前值,时间,报警信息,传感器状态,统计值,备注,上传否,上传否2,上传否3,上传否4,措施,原因,校准浓度) values ('" + alarmData.SensorId + "','" + alarmData.AlarmValue + "','" + alarmData.CurrentValue + "','" + alarmData.Time + "','" + alarmData.AlarmMsg + "','" + alarmData.Status + "','" + alarmData.CountValue + "','" + alarmData.Describe + "','" + alarmData.IsUpload + "','" + alarmData.IsUpload2 + "','" + alarmData.IsUpload3 + "','" + alarmData.IsUpload4 + "','" + alarmData.Guards + "','" + alarmData.Reason + "','" + alarmData.HotBack1 + "','" + alarmData.HotBack2 + "','" + alarmData.StandardizeSolubility + "')";
            //string cmdstr = "insert into 分级报警数据表" + tableDate + " values('" + alarmData.SensorId + "','" + alarmData.AlarmValue + "','" + alarmData.CurrentValue + "','" + alarmData.Time + "','" + alarmData.AlarmMsg + "','" + alarmData.Status + "','" + alarmData.CountValue + "','" + alarmData.Describe + "','" + alarmData.IsUpload + "','" + alarmData.IsUpload2 + "','" + alarmData.IsUpload3 + "','" + alarmData.IsUpload4 + "','" + alarmData.Guards + "','" + alarmData.Reason + "','" + alarmData.HotBack1 + "','" + alarmData.HotBack2 + "','" + alarmData.StandardizeSolubility + "')";
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }
        private void CreateAlertLevelTable(string yymmdd)
        {
            string cmdstr = "IF OBJECT_ID('分级报警数据表" + yymmdd + "','u') IS NULL BEGIN CREATE TABLE [dbo].[分级报警数据表" + yymmdd + "]( [系统序号] [int] NOT NULL, [报警值] [float] NULL, [当前值] [float] NULL, [时间] [datetime] NULL, [报警信息] [nvarchar](120) NULL, [传感器状态] [smallint] NULL, [统计值] [int] NULL, [备注] [ntext] NULL, [上传否] [bit] NULL, [上传否2] [bit] NULL, [上传否3] [bit] NULL, [上传否4] [bit] NULL, [措施] [nvarchar](255) NULL, [原因] [nvarchar](255) NULL,[热备状态1] [bit] NULL,[热备状态2] [bit] NULL, [校准浓度] [float] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] SELECT 1 END ELSE SELECT 0";
            localDB.SqDDLlExecute(cmdstr);
        }


        public void UpdateAlarmLevelData(AlarmLevelData alarmData, string Id, string tableDate, DateTime? time, string AlarmMsg)
        {
            string cmd = "update 分级报警数据表" + tableDate + "  set 报警值=" + alarmData.AlarmValue.ToString();
            cmd += ",系统序号=" + alarmData.SensorId.ToString();
            cmd += ",当前值=" + alarmData.CurrentValue.ToString();
            cmd += ",时间='" + alarmData.Time + "'";
            cmd += ",报警信息='" + alarmData.AlarmMsg.ToString() + "'";
            cmd += ",传感器状态=" + alarmData.Status.ToString();
            cmd += ",统计值='" + alarmData.CountValue + "'";
            cmd += ",备注='" + alarmData.Describe + "'";
            cmd += ",措施='" + alarmData.Guards + "'";
            cmd += ",原因='" + alarmData.Reason + "'";
            cmd += ",校准浓度='" + alarmData.StandardizeSolubility + "'";
            cmd += ",上传否=" + alarmData.IsUpload;
            cmd += ",上传否2=" + alarmData.IsUpload2;
            cmd += ",上传否3=" + alarmData.IsUpload3;
            cmd += ",上传否4=" + alarmData.IsUpload4;
            cmd += ",热备状态1='" + alarmData.HotBack1 + "'";
            cmd += ",热备状态2='" + alarmData.HotBack2 + "'";
            cmd += " where 系统序号=" + Id;
            cmd += " and 时间='" + time + "'";
            cmd += " and 报警信息='" + AlarmMsg + "'";
            localDB.SqlExecute(cmd);
        }
        #endregion

        /// <summary>
        /// 获取分站状态表   (查、插入)
        /// </summary>
        /// <returns></returns>
 	    #region 分站状态记录表
        public List<BrachStatus> GetBrachStatusList(int? stationId, DateTime startEateTime, DateTime? endDateTime, int? status)
        {
            DateTime date = startEateTime;
            string cmd = "select 分站地址 as BrachAdressId, 时间 as Time,数值 as Value,分站状态 as Status  from 分站状态记录表"+startEateTime.ToString("yyyyMMdd").Substring(2);
            for (int i = 1; date.AddDays(i)<=endDateTime; i++)
            {
                if(IsExistTable("分站状态记录表" + date.AddDays(i).ToString("yyyyMMdd").Substring(2)))
                    cmd += " union select 分站地址 as BrachAdressId, 时间 as Time,数值 as Value,分站状态 as Status  from 分站状态记录表" + date.AddDays(i).ToString("yyyyMMdd").Substring(2);
            }
            cmd+=" where 1=1 ";
            if (stationId != null)
                cmd += " and 分站地址=" + stationId + " ";
            if (startEateTime != null)
                cmd += " and 时间>='" + startEateTime + "' ";
            if (endDateTime != null)
                cmd += " and 时间<='" + endDateTime + "' ";
            if (status != null)
                cmd += " and 状态=" + status + " ";
            cmd += " order by 时间 DESC, 分站地址 ASC";
            List<BrachStatus> list = localDB.QueryList<BrachStatus>(cmd);
            return list;
        }

        //+ "','" + immediate.MaxValue
        public BrachStatus GetBrachStatustById(string BrachAdressId, string tableDate)
        {
            BrachStatus data = null;
            string cmdstr = "select 分站地址 as BrachAdressId, 时间 as Time,数值 as Value,分站状态 as Status from 分站状态记录表" + tableDate + " where 分站地址=" + BrachAdressId;
            cmdstr += " order by 时间 DESC";
            List<BrachStatus> list = localDB.QueryList<BrachStatus>(cmdstr);
            if (list.Count > 0)
                data = list.FirstOrDefault();
            return data;
        }

        public int AddBrachStatus(BrachStatus brachStatus, string tableDate)
        {
            string cmdstr = "insert into 分站状态记录表" + tableDate + " (分站地址,时间,分站状态,数值,上传否,上传否2,上传否3,上传否4) values('" + brachStatus.BrachAdressId + "','" + brachStatus.Time + "','" + brachStatus.Status + "','" + brachStatus.Value + "','" + brachStatus.IsUpload + "','" + brachStatus.IsUpload2 + "','" + brachStatus.IsUpload3 + "','" + brachStatus.IsUpload4 + "')";
            //  string cmdstr = "insert into 分站状态记录表" + tableDate + " values('" + brachStatus.BrachAdressId + "','" + brachStatus.Time + "','" + brachStatus.Status + "','" + brachStatus.Value + "','" + brachStatus.IsUpload + "','" + brachStatus.IsUpload2 + "','" + brachStatus.IsUpload3 + "','" + brachStatus.IsUpload4 + "')";
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }
        public void UpdateBrachStatus(BrachStatus brachStatus, string tableDate)
        {
            string cmd = "update 分站状态记录表" + tableDate;
            cmd += " set 数值=" + brachStatus.Value.ToString();
            cmd += ", 时间='" + brachStatus.Time + "'";
            cmd += " where 分站地址=" + brachStatus.BrachAdressId.ToString() + " and 时间 in  (select top 1 时间 from 分站状态记录表" + tableDate + " where   分站地址=" + brachStatus.BrachAdressId.ToString() + " order by 时间 DESC)";
            localDB.SqlExecute(cmd);
        }

        public void CreateBrachStatusTable(string yymmdd)
        {
            string cmdstr = "IF OBJECT_ID('分站状态记录表" + yymmdd + "','u') IS NULL BEGIN CREATE TABLE [dbo].[分站状态记录表" + yymmdd + "]( [分站地址] [smallint] NOT NULL, [时间] [datetime] NULL, [分站状态] [smallint] NULL, [数值] [float] NULL, [上传否] [bit] NULL, [上传否2] [bit] NULL, [上传否3] [bit] NULL, [上传否4] [bit] NULL) ON [PRIMARY] SELECT 1 END ELSE SELECT 0";
            localDB.SqDDLlExecute(cmdstr);
        }

        #endregion

        /// <summary>
        /// 获取Ddmm表   (查、插入)
        /// </summary>
        /// <returns></returns>
        #region Ddmm表
        public List<Ddmm> GetDdmmList(string tableDate)
        {
            string cmdstr = "select system_id as Systemid, DateTime as Datetime,min00 as Min00,ave00 as Ave00, max00 as Max00 ,min05 as Min05 ,ave05 as Ave05,max05 as Max05, min10 as Min10,ave10 as Ave10,max10 as Max10, min15 as Min15, ave15 as Ave15 , max15 as Max15,min20 as Min20, ave20 as Ave20,max20 as Max20,min25 as Min25,ave25 as Ave25,max25 as Max25,min30 as Min30,ave30 as Ave30,max30 as Max30,min35 as Min35,ave35 as Ave35,max35 as Max35,min40 as Min40,ave40 as Ave40,max40 as Max40,min45 as Min45,ave45 as Ave45,max45 as Max45,min50 as Min50,ave50 as Ave50,max50 as Max50,min55 as Min55,ave55 as Ave55,max55 as Max55,minhour as Minhour ,avehour as Avehour, maxhour as Maxhour  from ddmm_history" + tableDate;
            cmdstr += " order by system_id";
            List<Ddmm> list = localDB.QueryList<Ddmm>(cmdstr);
            return list;
        }
        public Ddmm GetDdmmBySensorId(string tableDate, int SensorId, DateTime time)
        {
            string cmdstr = "select count(1) from ddmm_history" + tableDate;
            cmdstr += " where system_id=" + SensorId.ToString() + " and Datetime='" + time + "'";
            int i = localDB.SqlExecuteScalar(cmdstr);
            if (i > 0)
            {
                cmdstr = "select system_id as Systemid, DateTime as Datetime,min00 as Min00,ave00 as Ave00, max00 as Max00 ,min05 as Min05 ,ave05 as Ave05,max05 as Max05, min10 as Min10,ave10 as Ave10,max10 as Max10, min15 as Min15, ave15 as Ave15 , max15 as Max15,min20 as Min20, ave20 as Ave20,max20 as Max20,min25 as Min25,ave25 as Ave25,max25 as Max25,min30 as Min30,ave30 as Ave30,max30 as Max30,min35 as Min35,ave35 as Ave35,max35 as Max35,min40 as Min40,ave40 as Ave40,max40 as Max40,min45 as Min45,ave45 as Ave45,max45 as Max45,min50 as Min50,ave50 as Ave50,max50 as Max50,min55 as Min55,ave55 as Ave55,max55 as Max55,minhour as Minhour ,avehour as Avehour, maxhour as Maxhour  from ddmm_history" + tableDate;
                cmdstr += " where system_id=" + SensorId.ToString() + " and Datetime='" + time + "' order by system_id";
                Ddmm data = localDB.GetSingle<Ddmm>(cmdstr);
                return data;
            }
            else
                return null;
        }
        //+ "','" + immediate.MaxValue
        public int AddDdmm(Ddmm ddmm, string tableDate)
        {
            if (!IsExistTable("ddmm_history" + tableDate))
                CreatDdmmDataTable(tableDate);
            //string cmdstr = "insert into ddmm_history" + tableDate + " values('" + ddmm.Systemid + "','" + ddmm.Datetime + "'," + (ddmm.Min00 == null ? @"null" : ddmm.Min00) + "," + (ddmm.Ave00 == null ? @"null" : ddmm.Ave00) + "," + (ddmm.Max00 == null ? @"null" : ddmm.Max00) + "," + (ddmm.Min05 == null ? @"null" : ddmm.Min05) + "," + (ddmm.Ave05 == null ? @"null" : ddmm.Ave05) + "," + (ddmm.Max05 == null ? @"null" : ddmm.Max05) + "," + (ddmm.Min10 == null ? @"null" : ddmm.Min10) + "," + (ddmm.Ave10 == null ? @"null" : ddmm.Ave10) + "," + (ddmm.Max10 == null ? @"null" : ddmm.Max10) + "," + (ddmm.Min15 == null ? @"null" : ddmm.Min15) + "," + (ddmm.Ave15 == null ? @"null" : ddmm.Ave15) + "," + (ddmm.Max15 == null ? @"null" : ddmm.Max15) + "," + (ddmm.Min20 == null ? @"null" : ddmm.Min20) + "," + (ddmm.Ave20 == null ? @"null" : ddmm.Ave20) + "," + (ddmm.Max20 == null ? @"null" : ddmm.Max20) + "," + (ddmm.Min25 == null ? @"null" : ddmm.Min25) + "," + (ddmm.Ave25 == null ? @"null" : ddmm.Ave25);
            //cmdstr += "," + (ddmm.Max25 == null ? @"null" : ddmm.Max25) + "," + (ddmm.Min30 == null ? @"null" : ddmm.Min25) + "," + (ddmm.Ave30 == null ? @"null" : ddmm.Max30) + "," + (ddmm.Max30 == null ? @"null" : ddmm.Max30) + "," + (ddmm.Min35 == null ? @"null" : ddmm.Min35) + "," + (ddmm.Ave35 == null ? @"null" : ddmm.Ave35) + "," + (ddmm.Max35 == null ? @"null" : ddmm.Max35);
            //cmdstr += "," + (ddmm.Min40 == null ? @"null" : ddmm.Min40) + "," + (ddmm.Ave40 == null ? @"null" : ddmm.Ave40) + "," + (ddmm.Max40 == null ? @"null" : ddmm.Max40) + "," + (ddmm.Min45 == null ? @"null" : ddmm.Min45) + "," + (ddmm.Ave45 == null ? @"null" : ddmm.Ave45) + "," + (ddmm.Max45 == null ? @"null" : ddmm.Max45) + "," + (ddmm.Min50 == null ? @"null" : ddmm.Min50) + "," + (ddmm.Ave50 == null ? @"null" : ddmm.Ave50) + "," + (ddmm.Max50 == null ? @"null" : ddmm.Max50) + "," + (ddmm.Min55 == null ? @"null" : ddmm.Min55) + "," + (ddmm.Ave55 == null ? @"null" : ddmm.Ave55) + "," + (ddmm.Max55 == null ? @"null" : ddmm.Max55) + "," + (ddmm.Minhour == null ? @"null" : ddmm.Minhour) + "," + (ddmm.Avehour == null ? @"null" : ddmm.Avehour) + "," + (ddmm.Maxhour == null ? @"null" : ddmm.Maxhour) + "," + (ddmm.IsUpload1 == null ? @"null" : ddmm.IsUpload1) + "," + (ddmm.IsUpload2 == null ? $"null" : ddmm.IsUpload2) + "," + (ddmm.IsUpload3 == null ? $"null" : ddmm.IsUpload3) + "," + (ddmm.IsUpload2 == null ? $"null" : ddmm.IsUpload4) + ")";

            string cmdstr = "insert into ddmm_history" + tableDate + "([system_id],[DateTime],[min00],[ave00],[max00],[min05],[ave05],[max05],[min10],[ave10],[max10],[min15],[ave15],[max15],[min20],[ave20],[max20],[min25],[ave25],[max25],[min30],[ave30],[max30],[min35],[ave35],[max35],[min40],[ave40],[max40],[min45],[ave45],[max45],[min50],[ave50],[max50],[min55],[ave55],[max55],[minhour],[avehour],[maxhour],[上传否],[上传否2],[上传否3],[上传否4]) " + "values('" + ddmm.Systemid + "','" + ddmm.Datetime + "'," + (ddmm.Min00 == null ? @"null" : ddmm.Min00) + "," + (ddmm.Ave00 == null ? @"null" : ddmm.Ave00) + "," + (ddmm.Max00 == null ? @"null" : ddmm.Max00) + "," + (ddmm.Min05 == null ? @"null" : ddmm.Min05) + "," + (ddmm.Ave05 == null ? @"null" : ddmm.Ave05) + "," + (ddmm.Max05 == null ? @"null" : ddmm.Max05) + "," + (ddmm.Min10 == null ? @"null" : ddmm.Min10) + "," + (ddmm.Ave10 == null ? @"null" : ddmm.Ave10) + "," + (ddmm.Max10 == null ? @"null" : ddmm.Max10) + "," + (ddmm.Min15 == null ? @"null" : ddmm.Min15) + "," + (ddmm.Ave15 == null ? @"null" : ddmm.Ave15) + "," + (ddmm.Max15 == null ? @"null" : ddmm.Max15) + "," + (ddmm.Min20 == null ? @"null" : ddmm.Min20) + "," + (ddmm.Ave20 == null ? @"null" : ddmm.Ave20) + "," + (ddmm.Max20 == null ? @"null" : ddmm.Max20) + "," + (ddmm.Min25 == null ? @"null" : ddmm.Min25) + "," + (ddmm.Ave25 == null ? @"null" : ddmm.Ave25);
            cmdstr += "," + (ddmm.Max25 == null ? @"null" : ddmm.Max25) + "," + (ddmm.Min30 == null ? @"null" : ddmm.Min25) + "," + (ddmm.Ave30 == null ? @"null" : ddmm.Max30) + "," + (ddmm.Max30 == null ? @"null" : ddmm.Max30) + "," + (ddmm.Min35 == null ? @"null" : ddmm.Min35) + "," + (ddmm.Ave35 == null ? @"null" : ddmm.Ave35) + "," + (ddmm.Max35 == null ? @"null" : ddmm.Max35);
            cmdstr += "," + (ddmm.Min40 == null ? @"null" : ddmm.Min40) + "," + (ddmm.Ave40 == null ? @"null" : ddmm.Ave40) + "," + (ddmm.Max40 == null ? @"null" : ddmm.Max40) + "," + (ddmm.Min45 == null ? @"null" : ddmm.Min45) + "," + (ddmm.Ave45 == null ? @"null" : ddmm.Ave45) + "," + (ddmm.Max45 == null ? @"null" : ddmm.Max45) + "," + (ddmm.Min50 == null ? @"null" : ddmm.Min50) + "," + (ddmm.Ave50 == null ? @"null" : ddmm.Ave50) + "," + (ddmm.Max50 == null ? @"null" : ddmm.Max50) + "," + (ddmm.Min55 == null ? @"null" : ddmm.Min55) + "," + (ddmm.Ave55 == null ? @"null" : ddmm.Ave55) + "," + (ddmm.Max55 == null ? @"null" : ddmm.Max55) + "," + (ddmm.Minhour == null ? @"null" : ddmm.Minhour) + "," + (ddmm.Avehour == null ? @"null" : ddmm.Avehour) + "," + (ddmm.Maxhour == null ? @"null" : ddmm.Maxhour) + "," + (ddmm.IsUpload1 == null ? @"null" : ddmm.IsUpload1) + "," + (ddmm.IsUpload2 == null ? $"null" : ddmm.IsUpload2) + "," + (ddmm.IsUpload3 == null ? $"null" : ddmm.IsUpload3) + "," + (ddmm.IsUpload2 == null ? $"null" : ddmm.IsUpload4) + ")";

            int i = localDB.SqlExecute(cmdstr);
            return i;

           //" (系统序号,报警值,当前值,时间,报警信息,传感器状态,统计值,备注,上传否,上传否2,上传否3,上传否4,措施,原因,校准浓度) values ('" + alarmData.SensorId + "','" + alarmData.AlarmValue + "','" + alarmData.CurrentValue + "','" + alarmData.Time + "','" + alarmData.AlarmMsg + "','" + alarmData.Status + "','" + alarmData.CountValue + "','" + alarmData.Describe + "','" + alarmData.IsUpload + "','" + alarmData.IsUpload2 + "','" + alarmData.IsUpload3 + "','" + alarmData.IsUpload4 + "','" + alarmData.Guards + "','" + alarmData.Reason + "','" + alarmData.HotBack1 + "','" + alarmData.HotBack2 + "','" + alarmData.StandardizeSolubility + "')";
        }

        public void UpdateDdmm(Ddmm ddmm, string tableDate)
        {
            string cmd = "update ddmm_history" + tableDate + " set DateTime='" + ddmm.Datetime + "'";
            cmd += ",min00=" + (ddmm.Min00 == null ? @"null" : ddmm.Min00);
            cmd += ",ave00=" + (ddmm.Ave00 == null ? @"null" : ddmm.Ave00);
            cmd += ",max00=" + (ddmm.Max00 == null ? @"null" : ddmm.Max00);
            cmd += ",min05=" + (ddmm.Min05 == null ? @"null" : ddmm.Min05);
            cmd += ",ave05=" + (ddmm.Ave05 == null ? @"null" : ddmm.Ave05);
            cmd += ",max05=" + (ddmm.Max05 == null ? @"null" : ddmm.Max05);
            cmd += ",min10=" + (ddmm.Min10 == null ? @"null" : ddmm.Min10);
            cmd += ",ave10=" + (ddmm.Ave10 == null ? @"null" : ddmm.Ave10);
            cmd += ",max10=" + (ddmm.Max10 == null ? @"null" : ddmm.Max10);
            cmd += ",min15=" + (ddmm.Min15 == null ? @"null" : ddmm.Min15);
            cmd += ",ave15=" + (ddmm.Ave15 == null ? @"null" : ddmm.Ave15);
            cmd += ",max15=" + (ddmm.Max15 == null ? @"null" : ddmm.Max15);
            cmd += ",min20=" + (ddmm.Min20 == null ? @"null" : ddmm.Min20);
            cmd += ",ave20=" + (ddmm.Ave20 == null ? @"null" : ddmm.Ave20);
            cmd += ",max20=" + (ddmm.Max20 == null ? @"null" : ddmm.Max20);
            cmd += ",min25=" + (ddmm.Min25 == null ? @"null" : ddmm.Min25);
            cmd += ",ave25=" + (ddmm.Ave25 == null ? @"null" : ddmm.Ave25);
            cmd += ",max25=" + (ddmm.Max25 == null ? @"null" : ddmm.Max25);
            cmd += ",min30=" + (ddmm.Min30 == null ? @"null" : ddmm.Min30);
            cmd += ",ave30=" + (ddmm.Ave30 == null ? @"null" : ddmm.Ave30);
            cmd += ",max30=" + (ddmm.Max30 == null ? @"null" : ddmm.Max30);
            cmd += ",min35=" + (ddmm.Min35 == null ? @"null" : ddmm.Min35);
            cmd += ",ave35=" + (ddmm.Ave35 == null ? @"null" : ddmm.Ave35);
            cmd += ",max35=" + (ddmm.Max35 == null ? @"null" : ddmm.Max35);
            cmd += ",min40=" + (ddmm.Min40 == null ? @"null" : ddmm.Min40);
            cmd += ",ave40=" + (ddmm.Ave40 == null ? @"null" : ddmm.Ave40);
            cmd += ",max40=" + (ddmm.Max40 == null ? @"null" : ddmm.Max40);
            cmd += ",min45=" + (ddmm.Min45 == null ? @"null" : ddmm.Min45);
            cmd += ",ave45=" + (ddmm.Ave45 == null ? @"null" : ddmm.Ave45);
            cmd += ",max45=" + (ddmm.Max45 == null ? @"null" : ddmm.Max45);
            cmd += ",min50=" + (ddmm.Min50 == null ? @"null" : ddmm.Min50);
            cmd += ",ave50=" + (ddmm.Ave50 == null ? @"null" : ddmm.Ave50);
            cmd += ",max50=" + (ddmm.Max50 == null ? @"null" : ddmm.Max50);
            cmd += ",min55=" + (ddmm.Min55 == null ? @"null" : ddmm.Min55);
            cmd += ",ave55=" + (ddmm.Ave55 == null ? @"null" : ddmm.Ave55);
            cmd += ",max55=" + (ddmm.Max55 == null ? @"null" : ddmm.Max55);
            cmd += ",minhour=" + (ddmm.Minhour == null ? @"null" : ddmm.Minhour);
            cmd += ",avehour=" + (ddmm.Avehour == null ? @"null" : ddmm.Avehour);
            cmd += ",maxhour=" + (ddmm.Maxhour == null ? @"null" : ddmm.Maxhour);
            cmd += " where system_id=" + ddmm.Systemid.ToString() + " and Datetime='" + ddmm.Datetime + "'";
            localDB.SqlExecute(cmd);
        }
        public void CreatDdmmDataTable(string yymmdd)
        {
            string cmdstr = "IF OBJECT_ID('ddmm_history" + yymmdd + "','u') IS NULL BEGIN CREATE TABLE [dbo].[ddmm_history" + yymmdd + "]( \t[system_id] [int] NOT NULL,\r\n\t[DateTime] [smalldatetime] NULL,\r\n\t[min00] [float] NULL,\r\n\t[ave00] [float] NULL,\r\n\t[max00] [float] NULL,";
            cmdstr += "\r\n\t[min05] [float] NULL,\r\n\t[ave05] [float] NULL,\r\n\t[max05] [float] NULL,\r\n\t[min10] [float] NULL,\r\n\t[ave10] [float] NULL,\r\n\t[max10] [float] NULL,\r\n\t[min15] [float] NULL,\r\n\t[ave15] [float] NULL,\r\n\t[max15] [float] NULL,";
            cmdstr += "\r\n\t[min20] [float] NULL,\r\n\t[ave20] [float] NULL,\r\n\t[max20] [float] NULL,\r\n\t[min25] [float] NULL,\r\n\t[ave25] [float] NULL,\r\n\t[max25] [float] NULL,\r\n\t[min30] [float] NULL,\r\n\t[ave30] [float] NULL,\r\n\t[max30] [float] NULL,";
            cmdstr += "\r\n\t[min35] [float] NULL,\r\n\t[ave35] [float] NULL,\r\n\t[max35] [float] NULL,\r\n\t[min40] [float] NULL,\r\n\t[ave40] [float] NULL,\r\n\t[max40] [float] NULL,\r\n\t[min45] [float] NULL,\r\n\t[ave45] [float] NULL,\r\n\t[max45] [float] NULL,";
            cmdstr += "\r\n\t[min50] [float] NULL,\r\n\t[ave50] [float] NULL,\r\n\t[max50] [float] NULL,\r\n\t[min55] [float] NULL,\r\n\t[ave55] [float] NULL,\r\n\t[max55] [float] NULL,\r\n\t[minhour] [float] NULL,\r\n\t[avehour] [float] NULL,\r\n\t[maxhour] [float] NULL,";
            cmdstr += "\r\n\t[上传否] [bit] NULL,\r\n\t[上传否2] [bit] NULL,\r\n\t[上传否3] [bit] NULL,\r\n\t[上传否4] [bit] NULL) ON [PRIMARY] SELECT 1 END ELSE SELECT 0";
            localDB.SqDDLlExecute(cmdstr);
        }
        #endregion


        /// <summary>
        ///  获取区域信息配置表 (查、插入、改、删)
        /// </summary>
        /// <returns></returns>
        /// 
        #region 区域信息配置表

        public AreaConfig GetAreaConfigById(string ID)
        {
            string cmdstr = "SELECT ID as ID,区域名称 as AreaName, 区域类型 as AreaType, 区域描述 as AreaDescription, 区域文件名称 as AreaFileName, 创建时间 as CreationTime FROM 区域信息配置表";
            cmdstr += " where ID=" + ID;
            AreaConfig areaConfig = localDB.GetSingle<AreaConfig>(cmdstr);
            return areaConfig;
        }

        public List<AreaConfig> GetAreaConfigListFromDB()
        {
            string cmdstr = "SELECT ID as ID,区域名称 as AreaName, 区域类型 as AreaType, 区域描述 as AreaDescription, 区域文件名称 as AreaFileName,创建时间 as CreationTime FROM 区域信息配置表";
            List<AreaConfig> list = localDB.QueryList<AreaConfig>(cmdstr);
            return list;
        }

        public int UpdateAreaConfig(AreaConfig areaConfig)
        {
            string cmdstr = "UPDATE 区域信息配置表 SET ID = '" + areaConfig.ID + "', 区域名称 = '" + areaConfig.AreaName + "', 区域类型 = '" + areaConfig.AreaType + "', 区域文件名称 = '" + areaConfig.AreaFileName + "',  区域描述 = '" + areaConfig.AreaDescription + "',创建时间 = '" + areaConfig.CreationTime + "' WHERE ID = '" + areaConfig.ID + "'";
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }

        public int AddAreaConfig(AreaConfig areaConfig)
        {
            string cmdstr = "INSERT INTO 区域信息配置表 (ID, 区域名称, 区域类型, 区域描述, 区域文件名称, 创建时间) VALUES ('" + areaConfig.ID + "', '" + areaConfig.AreaName + "', '" + areaConfig.AreaType + "', '" + areaConfig.AreaDescription + "', '" + areaConfig.AreaFileName + "', '" + areaConfig.CreationTime.ToString("yyyy-MM-dd HH:mm:ss.fff") + "')";
            int i = localDB.SqlExecute(cmdstr);
            return i;

        }

        public bool DeleteAreaConfig(string id)
        {
            string cmdstr = "DELETE FROM 区域信息配置表 WHERE ID = '" + id + "'";
            string detelePath = @"C:\Users\LQQ\Workspace\VSspace\rantserver-bigscreen\RantServer\Resources\";//保存路径
            string savename = "areaconfig" + id + ".png";
            string destinationPath = Path.Combine(detelePath, savename);

            try
            {
                // 尝试删除文件
                File.Delete(destinationPath);

                // 删除数据库记录
                int rowsAffected = localDB.SqlExecute(cmdstr);

                // 如果文件和数据库记录都成功删除，返回 true，否则返回 false
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                // 处理异常，如果删除失败，返回 false
                Console.WriteLine("删除失败: " + ex.Message);
                return false;
            }
        }


        #endregion


        /// <summary>
        /// 获取历史数据表   (查、插入)
        /// </summary>
        /// <returns></returns>
        #region 历史数据表
        //wang
        public List<HistoryData> GetHistoryDataList(int? sensorId, DateTime StartDate,DateTime EndDate)
        {
            DateTime date = StartDate;
            string cmdstr = "";
            for (int i = 0; date <= EndDate; i++)
            {
                if (IsExistTable("历史数据表" + date.AddDays(i).ToString("yyyyMMdd").Substring(2)))
                {
                    date = date.AddDays(i);
                    break;
                }
            }
            if (date > EndDate)
                return null;
            string tableName = "历史数据表"+ date.ToString("yyyyMMdd").Substring(2);
            cmdstr = "select  CONVERT(VARCHAR(50),端口参数表.分站地址)+'-'+CONVERT(VARCHAR(50),端口参数表.端口序号) as PortStr,端口参数表.传感器名称 as Type,端口参数表.安装地点 as Location, "+tableName+".系统序号 as SensorId,当前值 as CurValue,时间 as Time,时间 as Time2,"+tableName+".上传否 as IsUpload ,"+tableName+".上传否2 as IsUpload2 ,"+tableName+".上传否3 as IsUpload3 ,"+tableName+".上传否4 as IsUpload4 , 数据状态 as Status  from " + tableName;
            cmdstr += "    inner join 端口参数表  on " + tableName + ".系统序号 = 端口参数表.系统序号  ";
            for (int i = 1; date.AddDays(i) <= EndDate; i++)
            {
                tableName = "历史数据表" + date.AddDays(i).ToString("yyyyMMdd").Substring(2);
                if (IsExistTable(tableName))
                {

                    cmdstr += " union select  CONVERT(VARCHAR(50),端口参数表.分站地址)+'-'+CONVERT(VARCHAR(50),端口参数表.端口序号) as PortStr,端口参数表.传感器名称 as Type,端口参数表.安装地点 as Location, " + tableName + ".系统序号 as SensorId,当前值 as CurValue,时间 as Time,时间 as Time2," + tableName + ".上传否 as IsUpload ," + tableName + ".上传否2 as IsUpload2 ," + tableName + ".上传否3 as IsUpload3 ," + tableName + ".上传否4 as IsUpload4 , 数据状态 as Status  from " + tableName;
                    cmdstr += "    inner join 端口参数表  on " + tableName + ".系统序号 = 端口参数表.系统序号  ";
                }
            }
            cmdstr += " where 数据状态<>-1";
            if (sensorId != null)
                cmdstr += " and 端口参数表.系统序号=" + sensorId + " ";
            if (StartDate != null)
                cmdstr += " and 时间>='" + StartDate + "' ";
            if (EndDate != null)
                cmdstr += " and 时间<='" + EndDate + "' ";
            cmdstr += " ORDER by 时间 DESC";
            List<HistoryData> list = localDB.QueryList<HistoryData>(cmdstr);
            return list;
        }
        public List<HistoryData> GetHistoryDataListBySenorId(string tableDate,string sensorId)
        {
            string cmdstr = "select  系统序号 as SensorId,当前值 as CurValue,时间 as Time,时间 as Time2,上传否 as IsUpload ,上传否2 as IsUpload2 ,上传否3 as IsUpload3 ,上传否4 as IsUpload4 , 数据状态 as Status   from 历史数据表" + tableDate +" where 系统序号 = "+ sensorId;
            cmdstr += " and  数据状态<>-1 order by 时间";
            List<HistoryData> list = localDB.QueryList<HistoryData>(cmdstr);
            if (list == null)
            {
                cmdstr = "select 系统序号 as SensorId,当前值 as CurValue,时间 as Time,时间 as Time2,上传否 as IsUpload ,上传否2 as IsUpload2 ,上传否3 as IsUpload3 ,上传否4 as IsUpload4 , 数据状态 as Status  from 历史数据表" + tableDate + " where 系统序号 = " + sensorId; 
                cmdstr += " and  数据状态<>-1 order by 时间";
                list = localDB.QueryList<HistoryData>(cmdstr);
            }
            return list;
        }
        public List<HistoryDisplayData> GetHistoryDisplayDataListBySenorId(string tableDate, string sensorId)
        {
            string cmdstr = "select cast(b.分站地址 as varchar)  + '-' + cast(b.端口序号 as varchar)   as stationName,b.分站地址 as StationId, b.系统序号 as SensorId,b.传感器名称 as Name,a.当前值 as CurValue , a.时间 as Time, 数据状态 as Status  from 历史数据表" + tableDate+ " a  join 端口参数表 b on a.系统序号=b.系统序号  where a.系统序号 = " + sensorId +" order by a.时间";
            List<HistoryDisplayData> list = localDB.QueryList<HistoryDisplayData>(cmdstr);
            return list;
        }
        public bool IsExistRecord(string tableDate, int SensorId, DateTime time)
        {
            string cmdstr = "select  系统序号 as SensorId,当前值 as CurValue,时间 as Time,时间 as Time2,上传否 as IsUpload ,上传否2 as IsUpload2 ,上传否3 as IsUpload3 ,上传否4 as IsUpload4, 数据状态 as Status   from 历史数据表" + tableDate + " where 系统序号 = " + SensorId +"  and 时间>'"+time+"'";
            List<HistoryData> list = localDB.QueryList<HistoryData>(cmdstr);
            if (list == null || list.Count < 1)
                return false;
            else
                return true;
        }
        //wang
        public HistoryData GetHistoryDataById(string Id, string tableDate, DateTime? date)
        {
            string cmdstr = "select 系统序号 as SensorId,当前值 as CurValue,时间 as Time,上传否 as IsUpload ,上传否2 as IsUpload2 ,上传否3 as IsUpload3 ,上传否4 as IsUpload4, 数据状态 as Status  from 历史数据表" + tableDate;
            cmdstr += " where 系统序号=" + Id;
            if (date != null)
            {
                cmdstr += " and 数据状态<>-1 and 时间='" + date + "'";
            }
            cmdstr += " order by 时间";
            HistoryData HistoryData = localDB.QueryList<HistoryData>(cmdstr).FirstOrDefault();
            return HistoryData;
        }
        public void UpdateAbnormalData(string tableDate,DateTime startTime,int sensorId)
        {
            string cmd = "update 历史数据表"+tableDate+" set 数据状态='-1' where 系统序号="+sensorId.ToString()+" and 时间>='"+ startTime.ToString("yyyy-MM-dd HH:mm:ss.fff") +"'";
            localDB.SqlExecute(cmd);
        }
        public List<HistoryData> GetHistoryDataDuration(DateTime StartTable, DateTime EndTable, int SensorId,int granularity,int isAnalog)
        {
            List<HistoryData> list;
            list = localDB.GetListFromStoreProcedure<HistoryData>("GenerateChartsData", StartTable, EndTable, SensorId.ToString(), granularity, isAnalog);
            return list;
        }

        /// <summary>
        /// 获取一个时间段内的历史数据
        /// </summary>
        /// <param name="StartTable">起始时间</param>
        /// <param name="EndTable">结束时间</param>
        /// <param name="SensorId">传感器系统序号</param>
        /// <param name="Minutes">分钟数，如果为0，则按照秒分组，否则，按照Minutes分钟分组</param>
        /// <returns></returns>
        public List<HistoryData> GetHistoryDataLongDuration(DateTime StartTable, DateTime EndTable, int SensorId, int isAnalog,bool autoExtend=false)
        {
            DateTime date = StartTable;
            List<HistoryData> list;
            int granularity=16;
            if ((EndTable - StartTable).TotalDays >= 90)
                granularity = 13;
            if ((EndTable - StartTable).TotalDays >= 29)
                granularity = 15;
            else if ((EndTable - StartTable).TotalHours >= 20)
                granularity = 16;
            else if ((EndTable - StartTable).TotalDays < 1)
                granularity = 19;

            list = GetHistoryDataDuration(StartTable, EndTable, SensorId,granularity,isAnalog);
            return list;

        }
        //+ "','" + immediate.MaxValue
        public int AddHistoryData(HistoryData historyData, string tableDate)
        {
            if (!IsExistTable("历史数据表" + tableDate))
            { 
                CreateHistoryDataTable(tableDate);
                CreateAlertTable(tableDate);
            }
            string cmdstr = "insert into 历史数据表" + tableDate + " (系统序号,当前值,时间,时间2,上传否,上传否2,上传否3,上传否4,数据状态) values (" + historyData.SensorId + "," + historyData.CurValue + ",'" +  historyData.Time.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'," + (historyData.Time2 == new DateTime(0) ? @"null" : "'"+historyData.Time2.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'") + "," + historyData.IsUpload + "," + historyData.IsUpload2 + "," + historyData.IsUpload3 + "," + historyData.IsUpload4 + ","+historyData.Status+")";
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }
        public int AddAIHistoryData(HistoryData historyData, string tableDate)
        {
            if (!IsExistTable("历史数据表AI" + tableDate))
            {
                CreateHistoryDataTable(tableDate);
            }
            string cmdstr = "insert into 历史数据表AI" + tableDate + " (系统序号,当前值,时间,时间2,上传否,上传否2,上传否3,上传否4,数据状态) values (" + historyData.SensorId + "," + historyData.CurValue + ",'" + historyData.Time.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'," + (historyData.Time2 == new DateTime(0) ? @"null" : "'" + historyData.Time2.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'") + "," + historyData.IsUpload + "," + historyData.IsUpload2 + "," + historyData.IsUpload3 + "," + historyData.IsUpload4 + "," + historyData.Status + ")";
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }
        public int AddBatchHistoryData(List<Immediate> immediates, string tableDate,DateTime time, DateTime time2)
        {
            //待修改，从上一天的数据中获取最后一行
            if (!IsExistTable("历史数据表" + tableDate))
                CreateHistoryDataTable(tableDate);
            string cmdstr = "";
            List<PortPara> sensors = GetPortParaList();
            foreach (var v in immediates)
            {
                if (sensors.Find(p => p.SensorId == v.SensorId) != null)
                    cmdstr += "insert into 历史数据表" + tableDate + " (系统序号,当前值,时间,时间2,上传否,上传否2,上传否3,上传否4,数据状态) values (" + v.SensorId + "," + v.CurrentValue + ",'" + time + "','" + time2 + "',0,0,0,0,"+ v.DataStatus+");\n";
                else
                    Console.WriteLine("ID:" + v.SensorId + " " + v.SensorName);
            }
            if (cmdstr == string.Empty)
                return 0;
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }
        public void UpdateHistoryData(HistoryData historyData, string tableDate)
        {
            string cmd = "update 历史数据表" + tableDate + " set 当前值=" + historyData.CurValue.ToString();
            cmd += ",Time='" + historyData.Time + "'";
            cmd += " where 系统序号=" + historyData.SensorId.ToString();
            localDB.SqlExecute(cmd);
        }
        //wang
        public void UpdateHistoryData(HistoryData historyData, string Id, string tableDate, DateTime? time)
        {
            string cmd = "update 历史数据表" + tableDate + " set 当前值=" + historyData.CurValue.ToString();
            cmd += ",时间='" + historyData.Time + "'";
            cmd += ",上传否=" + historyData.IsUpload;
            cmd += ",上传否2=" + historyData.IsUpload2;
            cmd += ",上传否3=" + historyData.IsUpload3;
            cmd += ",上传否4=" + historyData.IsUpload4;
            cmd += " where 系统序号=" + Id;
            if (time != null)
            {
                cmd += " and 时间='" + time + "'";
            }
            localDB.SqlExecute(cmd);
        }
        public void CreateHistoryDataTable(string yymmdd)
        {
            string cmdstr = "IF OBJECT_ID('历史数据表AI" + yymmdd + "','u') IS NULL BEGIN CREATE TABLE [dbo].[历史数据表AI" + yymmdd + "]( \t[系统序号] [int] NOT NULL,\r\n\t[当前值] [float] NULL,\r\n\t[时间] [datetime] NULL,\r\n\t[时间2] [datetime] NULL,\r\n\t[上传否] [bit] NULL,\r\n\t[上传否2] [bit] NULL,\r\n\t[上传否3] [bit] NULL,\r\n\t[上传否4] [bit] NULL,\r\n\t[热备状态1] [bit] NULL,\r\n\t[热备状态2] [bit] NULL,\r\n\t[数据状态]  [int] NULL) ON [PRIMARY] SELECT 1 END ELSE SELECT 0";
            localDB.SqDDLlExecute(cmdstr);
        }
        /// <summary>
        /// 补齐数据记录成为连续的序列
        /// </summary>
        /// <param name="list"></param>
        /// <param name="grain">粒度，0为秒，1为分钟</param>
        ///  <param name="TimeUnit">时间间隔数值</param>
        /// <returns></returns>
        public List<HistoryData> ExtendAnalogList(List<HistoryData> sourcelist, int grain = 0,int TimeUnit=5)
        {
            int count = sourcelist.Count;
            HistoryData historyData = new HistoryData();
            double duration;
            if (grain == 1)
            {
                //DateTime date = sourcelist[count - 1].Time;
                //DateTime nextDay = DateTime.ParseExact(date.ToString("yyyyMMdd"), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture).AddDays(1);
                //historyData.Time = nextDay;
                //historyData.CurValue = sourcelist[count - 1].CurValue;
                //sourcelist.Add(historyData);

                //date = DateTime.ParseExact(sourcelist[0].Time.ToString("yyyyMMdd"), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture).AddMilliseconds(-1);
                //historyData = new HistoryData();
                //historyData.CurValue = sourcelist[0].CurValue;
                //historyData.Time = date;
                //sourcelist.Insert(0, historyData);
            }
            count = sourcelist.Count;
            List<HistoryData> ret = new List<HistoryData>();
            for (int i = 0; i < count - 1; i++)
            {
                ret.Add(sourcelist[i]);
                if (sourcelist[i].Time != sourcelist[i + 1].Time)
                {
                    TimeSpan ts = sourcelist[i + 1].Time - sourcelist[i].Time;
                    if (grain == 0)
                        duration = ts.TotalSeconds;
                    else
                        duration = ts.TotalMinutes;
                    for (int j = 0; j < duration/TimeUnit - 1; j++)
                    {
                        HistoryData data = sourcelist[i].Clone();
                        if (grain == 0)
                            data.Time = data.Time.AddSeconds(j*TimeUnit + 1);
                        else
                            data.Time = data.Time.AddMinutes(j*TimeUnit + 1);
                        //Console.WriteLine(data.Time.ToString("yyyy-MM-dd HH:mm:ss"));
                        ret.Add(data);
                    }
                }
            }
            return ret;
        }
        public async Task<List<HistoryData>> AsyncExtendAnalogList(List<HistoryData> sourcelist, int grain = 0, int TimeUnit = 5)
        {
            int count = sourcelist.Count;
            HistoryData historyData = new HistoryData();
            double duration;
            if (grain == 1)
            {
                //DateTime date = sourcelist[count - 1].Time;
                //DateTime nextDay = DateTime.ParseExact(date.ToString("yyyyMMdd"), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture).AddDays(1);
                //historyData.Time = nextDay;
                //historyData.CurValue = sourcelist[count - 1].CurValue;
                //sourcelist.Add(historyData);

                //date = DateTime.ParseExact(sourcelist[0].Time.ToString("yyyyMMdd"), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture).AddMilliseconds(-1);
                //historyData = new HistoryData();
                //historyData.CurValue = sourcelist[0].CurValue;
                //historyData.Time = date;
                //sourcelist.Insert(0, historyData);
            }
            count = sourcelist.Count;
            List<HistoryData> ret = new List<HistoryData>();
            await Task.Run(() =>
            {
                for (int i = 0; i < count - 1; i++)
                {
                    ret.Add(sourcelist[i]);
                    if (sourcelist[i].Time != sourcelist[i + 1].Time)
                    {
                        TimeSpan ts = sourcelist[i + 1].Time - sourcelist[i].Time;
                        if (grain == 0)
                            duration = ts.TotalSeconds;
                        else
                            duration = ts.TotalMinutes;
                        for (int j = 0; j < duration / TimeUnit - 1; j++)
                        {
                            HistoryData data = sourcelist[i].Clone();
                            if (grain == 0)
                                data.Time = data.Time.AddSeconds(j * TimeUnit + 1);
                            else
                                data.Time = data.Time.AddMinutes(j * TimeUnit + 1);
                            //Console.WriteLine(data.Time.ToString("yyyy-MM-dd HH:mm:ss"));
                            ret.Add(data);
                        }
                    }
                }
            });
            return ret;
        }
        /// <summary>
        /// 按照端口参数表报警上限将数据分段
        /// </summary>
        /// <param name="list"></param>
        /// <param name="port"></param>
        /// <param name="LimitType">1：报警，2：断电</param>
        /// <returns></returns>
        public List<List<HistoryData>> GetAnalogHistoryDataSegments(List<HistoryData> list, PortPara port, int LimitType = 1)
        {
            HistoryData first = list[0];
            float value = first.CurValue;
            float? throsheld;
            if (LimitType == 1)
                throsheld = port.HighValue;
            else
                throsheld = port.PowerValue;
            bool isNormal=value<throsheld.Value;
            List<List<HistoryData>> SegmentsList = new List<List<HistoryData>>();
            if (port.IsWarning == 0)
            {
                SegmentsList.Add(list);
                return SegmentsList;
            }
            List<HistoryData> Segments = new List<HistoryData>();
            for (int i = 0; i < list.Count; i++)
            {
                if (list[i].CurValue < throsheld == isNormal)
                    Segments.Add(list[i]);
                else
                {
                    if(isNormal)
                        Segments.Add(list[i]);
                    SegmentsList.Add(Segments);
                    Segments = new List<HistoryData>();
                    if (!isNormal)
                        Segments.Add(list[i-1]);
                    Segments.Add(list[i]);
                    isNormal=!isNormal;
                }

            }
            SegmentsList.Add(Segments);
            return SegmentsList;
        }
        /// <summary>
        /// 按照端口参数表报警上限将数据分段
        /// </summary>
        /// <param name="list"></param>
        /// <param name="port"></param>
        /// <param name="LimitType">1：报警，2：断电</param>
        /// <returns></returns>
        public List<List<HistoryData>> GetSwitchHistoryDataSegments(List<HistoryData> list, PortPara port, int LimitType=1)
        {
            HistoryData first = list[0];
            float value = first.CurValue;

            List<List<HistoryData>> SegmentsList = new List<List<HistoryData>>();
            List<HistoryData> Segments = new List<HistoryData>();
            for (int i = 0; i < list.Count; i++)
            {
                if (list[i].CurValue == value)
                    Segments.Add(list[i]);
                else {
                    value=list[i].CurValue; 
                    HistoryData data = list[i].Clone();
                    data.CurValue = list[i-1].CurValue;
                    Segments.Add(data);
                    SegmentsList.Add(Segments);
                    Segments = new List<HistoryData>();
                    //if (!isNormal)
                        Segments.Add(list[i - 1]);
                    Segments.Add(list[i]);
                }
            }
            SegmentsList.Add(Segments);
            return SegmentsList;
        }
        #endregion

        /// <summary>
        /// 获取断复电记录表   (查、插入)
        /// </summary>
        /// <returns></returns>
        #region 断复电记录表
        //wang
        public List<OffPower> GetOffPowerList(string tableDate)
        {
            string cmdstr = "select CONVERT(VARCHAR(50),b.分站地址)+'-'+CONVERT(VARCHAR(50),b.端口序号) as PortStr,b.传感器名称 as Type,b.安装地点 as Location, a.系统序号 as SensorId,数值 as Value,a.分站地址 as BrachAdressId,开出端口号 as OpenPort ,断电否 as IsPowerOff,时间 as Time,断电信息 as PowerOffMsg,备注 as Describe  from 断复电记录表" + tableDate;
            cmdstr += " a  inner join 端口参数表 b on a.系统序号=b.系统序号  order by 时间 desc";
            List<OffPower> list = localDB.QueryList<OffPower>(cmdstr);

            //if (list == null)
            //{
            //    cmdstr = "select  系统序号 as SensorId,数值 as Value,分站地址 as BrachAdressId,开出端口号 as OpenPort ,断电否 as IsPowerOff,时间 as Time,断电信息 as PowerOffMsg,备注 as Describe,上传否 as IsUpload,上传否2 as IsUpload2,上传否3 as IsUpload3,上传否4 as IsUpload4  from 断复电记录表" + tableDate;
            //    cmdstr += " order by 系统序号";
            //    list = db.QueryList<OffPower>(cmdstr);
            //}
            return list;
        }

        //wang
        public OffPower GetOffPowerById(string Id, string tableDate, DateTime? date)
        {
            string cmdstr = "select  系统序号 as SensorId,数值 as Value,分站地址 as BrachAdressId,时间 as Time,开出端口号 as OpenPort ,断电否 as IsPowerOff,断电信息 as PowerOffMsg ,备注 as Describe,上传否 as IsUpload,上传否2 as IsUpload2,上传否3 as IsUpload3,上传否4 as IsUpload4   from 断复电记录表" + tableDate;
            cmdstr += " where 系统序号=" + Id;
            if (date != null)
            {
                cmdstr += " and 时间='" + date + "'";
            }
            OffPower OffPower = localDB.GetSingle<OffPower>(cmdstr);
            return OffPower;
        }


        //+ "','" + offPower.MaxValue
        public int AddOffPower(OffPower offPower, string tableDate)
        {
            if (!IsExistTable("断复电记录表" + tableDate))
                CreateOffPowerTable(tableDate);
            string cmdstr = "insert into 断复电记录表" + tableDate + " (系统序号,数值,分站地址,开出端口号,断电否,时间,断电信息,备注,上传否,上传否2,上传否3,上传否4) values('" + offPower.SensorId + "','" + offPower.Value + "','" + offPower.BrachAdressId + "','" + offPower.OpenPort + "','" + offPower.IsPowerOff + "','" + offPower.Time + "','" + offPower.PowerOffMsg + "','" + offPower.Describe + "','" + offPower.IsUpload + "','" + offPower.IsUpload2 + "','" + offPower.IsUpload3 + "','" + offPower.IsUpload4 + "')";
            //string cmdstr = "insert into 断复电记录表" + tableDate + " values('" + offPower.SensorId + "','" + offPower.Value + "','" + offPower.BrachAdressId + "','" + offPower.OpenPort + "','" + offPower.IsPowerOff + "','" + offPower.Time + "','" + offPower.PowerOffMsg + "','" + offPower.Describe + "','" + offPower.IsUpload + "','" + offPower.IsUpload2 + "','" + offPower.IsUpload3 + "','" + offPower.IsUpload4 + "')";
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }

        public void UpdateOffPower(OffPower offPower, string tableDate)
        {
            string cmd = "update 断复电记录表" + tableDate + " set 数值=" + offPower.Value.ToString();
            cmd += ",分站地址=" + offPower.BrachAdressId.ToString();
            cmd += ",Time='" + offPower.Time + "'";
            cmd += ",开出端口号=" + offPower.OpenPort.ToString();
            cmd += ",断电否=" + offPower.IsPowerOff.ToString();
            cmd += ",时间=" + offPower.Time + "";
            cmd += ",断电信息=" + offPower.PowerOffMsg.ToString();
            cmd += ",备注='" + offPower.Describe + "'";
            cmd += " where 系统序号=" + offPower.SensorId.ToString();
            localDB.SqlExecute(cmd);
        }
        //wang
        public void UpdateOffPower(OffPower offPower, string Id, string tableDate, DateTime? time)
        {
            string cmd = "update 断复电记录表" + tableDate + " set 数值=" + offPower.Value.ToString();
            cmd += ",分站地址=" + offPower.BrachAdressId.ToString();
            cmd += ",时间='" + offPower.Time + "'";
            cmd += ",开出端口号=" + offPower.OpenPort.ToString();
            cmd += ",断电否=" + offPower.IsPowerOff.ToString();
            cmd += ",断电信息='" + offPower.PowerOffMsg.ToString() + "'";
            cmd += ",备注='" + offPower.Describe + "'";
            cmd += ",上传否=" + offPower.IsUpload;
            cmd += ",上传否2=" + offPower.IsUpload2;
            cmd += ",上传否3=" + offPower.IsUpload3;
            cmd += ",上传否4=" + offPower.IsUpload4;
            cmd += " where 系统序号=" + Id;
            if (time != null)
            {
                cmd += " and 时间='" + time + "'";
            }
            localDB.SqlExecute(cmd);
        }
        public  void CreateOffPowerTable(string yymmdd)
        {
            string cmdstr = "IF OBJECT_ID('断复电记录表" + yymmdd + "','u') IS NULL BEGIN CREATE TABLE [dbo].[断复电记录表" + yymmdd + "]( [系统序号] [int] NOT NULL, [数值] [float] NULL, [分站地址] [smallint] NULL, [开出端口号] [smallint] NULL, [断电否] [bit] NULL, [时间] [datetime] NULL, [断电信息] [nvarchar](50) NULL, [备注] [ntext] NULL, [上传否] [bit] NULL, [上传否2] [bit] NULL, [上传否3] [bit] NULL, [上传否4] [bit] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] SELECT 1 END ELSE SELECT 0";
            localDB.SqDDLlExecute(cmdstr);
        }
        #endregion

        /// <summary>
        /// 获取端口参数表   (查、插入)
        /// </summary>
        /// <returns></returns>
        #region 端口参数表
        public List<PortPara> GetPortParaList()
        {
            string cmdstr = "select 系统序号 as SensorId,是否已删除 as IsDelete ,是否有效 as IsValid,传感器名称 as SensorName ,传感器类型 as SensorType,传感器规格型号 as SensorModel ,安装地点 as InstallLocation";
            cmdstr += ",分站地址 as BranchAddr,端口序号 as PortId ,是否模拟量 as IsAnalog,停显示 as StopDisplay,开显示 as OpenDisplay,单位 as Unit ,系数  as coefficient ,基值 as BaseValue ,是否报警 as IsWarning";
            cmdstr += ",报警方式 as WarningType,报警上限值 as HighValue ,报警上限解除值 as HighRemoveValue,报警下限值 as LowValue,报警下限解除值 as LowRemoveValue ,是否断电控制 as IsPowerControl ,是否终端断电  as IsTerminal";
            cmdstr += " ,断电方式 as PowerMethod ,异常值判定比率 as AbnormalRatio,异常值判定间隔 as IntervalOfOutliers,量程下限 as LowLimitRange,量程上限 as HighLimitRange,断电值 as PowerValue,设备编号 as EquipmentId";
            cmdstr += " ,分级限时1 as ClassifyTime1,分级限时2 as ClassifyTime2,分级限时3 as ClassifyTime3,分级限时4 as ClassifyTime4,分级下限1 as LowClassify1,分级下限2 as LowClassify2,分级下限3 as LowClassify3,分级下限4 as LowClassify4";
            cmdstr += " ,上次标校时间 as LastTime1,安装地点编号 as InstallLocationId, 忽略故障 as IsIgnore, 工作面类型 as WorkType,甲烷传感器编号 as GasSensorId,标校周期 as MarkingCycle,截面积 as CrossSection,断电值2 as PowerValue2 ,二次断电端口1 as TwoPowerOffPort1,二次断电端口2 as TwoPowerOffPort2 ,安装时间 as InstallTime,删除时间 as DeleteTime1";
            cmdstr += " ,是否分级报警 as IsGradingAlarm,分级上限1 as HighClassify1 ,分级上限2 as HighClassify2, 分级上限3 as HighClassify3,分级上限4 as HighClassify4 , 复电值 as RePowerValue ,是否多路控制 as IsMultiControl, 断电控制端口 as PowerOffPort,断电区域 as PowerArea,关联传感器 as RelateSensor  from 端口参数表 ";
            cmdstr += " order by 分站地址,端口序号";
            //List<PortPara> list = db.QueryCacheList<PortPara>(PortConfigKey, cmdstr);
            List<PortPara> list = remoteDB.QueryCacheList<PortPara>(PortConfigKey, cmdstr);
            return list;
        }
        /// <summary>
        /// 主从同步端口参数表
        /// </summary>
        /// <param name="list"></param>
        public void SyncPortPara(List<PortPara> list)
        {
            string cmd = "delete from 端口参数表; ";
            localDB.SqlExecute(cmd);
            foreach (var v in list)
            {
                if (v.SensorId < 1000)
                    cmd = "";
                AddPortPara(v,false);
            }
            LocalCache.Remove(PortConfigKey);
        }
        public List<PortPara> GetPortParaListFromDB()
        {
            string cmdstr = "select 系统序号 as SensorId,是否已删除 as IsDelete ,是否有效 as IsValid,传感器名称 as SensorName ,传感器类型 as SensorType,传感器规格型号 as SensorModel ,安装地点 as InstallLocation";
            cmdstr += ",分站地址 as BranchAddr,端口序号 as PortId ,是否模拟量 as IsAnalog,停显示 as StopDisplay,开显示 as OpenDisplay,单位 as Unit ,系数  as coefficient ,基值 as BaseValue ,是否报警 as IsWarning";
            cmdstr += ",报警方式 as WarningType,报警上限值 as HighValue ,报警上限解除值 as HighRemoveValue,报警下限值 as LowValue,报警下限解除值 as LowRemoveValue ,是否断电控制 as IsPowerControl ,是否终端断电  as IsTerminal";
            cmdstr += " ,断电方式 as PowerMethod ,异常值判定比率 as AbnormalRatio,异常值判定间隔 as IntervalOfOutliers,量程下限 as LowLimitRange,量程上限 as HighLimitRange,断电值 as PowerValue,设备编号 as EquipmentId";
            cmdstr += " ,分级限时1 as ClassifyTime1,分级限时2 as ClassifyTime2,分级限时3 as ClassifyTime3,分级限时4 as ClassifyTime4,分级下限1 as LowClassify1,分级下限2 as LowClassify2,分级下限3 as LowClassify3,分级下限4 as LowClassify4";
            cmdstr += " ,上次标校时间 as LastTime1,安装地点编号 as InstallLocationId, 忽略故障 as IsIgnore, 工作面类型 as WorkType,甲烷传感器编号 as GasSensorId,标校周期 as MarkingCycle,截面积 as CrossSection,断电值2 as PowerValue2 ,二次断电端口1 as TwoPowerOffPort1,二次断电端口2 as TwoPowerOffPort2 ,安装时间 as InstallTime,删除时间 as DeleteTime1";
            cmdstr += " ,是否分级报警 as IsGradingAlarm,分级上限1 as HighClassify1 ,分级上限2 as HighClassify2, 分级上限3 as HighClassify3,分级上限4 as HighClassify4 , 复电值 as RePowerValue ,是否多路控制 as IsMultiControl, 断电控制端口 as PowerOffPort,断电区域 as PowerArea,关联传感器 as RelateSensor , 摄像头IP as CameraIp, 摄像头用户名 as CameraUser, 摄像头密码 as CameraPass from 端口参数表 ";
            cmdstr += " order by 分站地址,端口序号";
            //List<PortPara> list = db.QueryList<PortPara>( cmdstr);
            List<PortPara> list = remoteDB.QueryList<PortPara>(cmdstr);
            return list;
        }
        public List<PortPara> GetPortParaList(bool onlyIsDelete, bool onlyIsValid, string sensorType="")
        {
            string cmdstr = "select 系统序号 as SensorId,是否已删除 as IsDelete ,是否有效 as IsValid,传感器名称 as SensorName ,传感器类型 as SensorType,传感器规格型号 as SensorModel ,安装地点 as InstallLocation";
            cmdstr += ",分站地址 as BranchAddr,端口序号 as PortId ,是否模拟量 as IsAnalog,停显示 as StopDisplay,开显示 as OpenDisplay,单位 as Unit ,系数  as coefficient ,基值 as BaseValue ,是否报警 as IsWarning";
            cmdstr += ",报警方式 as WarningType,报警上限值 as HighValue ,报警上限解除值 as HighRemoveValue,报警下限值 as LowValue,报警下限解除值 as LowRemoveValue ,是否断电控制 as IsPowerControl ,是否终端断电  as IsTerminal";
            cmdstr += " ,断电方式 as PowerMethod ,异常值判定比率 as AbnormalRatio,异常值判定间隔 as IntervalOfOutliers,量程下限 as LowLimitRange,量程上限 as HighLimitRange,断电值 as PowerValue,设备编号 as EquipmentId";
            cmdstr += " ,分级限时1 as ClassifyTime1,分级限时2 as ClassifyTime2,分级限时3 as ClassifyTime3,分级限时4 as ClassifyTime4,分级下限1 as LowClassify1,分级下限2 as LowClassify2,分级下限3 as LowClassify3,分级下限4 as LowClassify4";
            cmdstr += " ,上次标校时间 as LastTime1,安装地点编号 as InstallLocationId, 忽略故障 as IsIgnore, 工作面类型 as WorkType,甲烷传感器编号 as GasSensorId,标校周期 as MarkingCycle,截面积 as CrossSection,断电值2 as PowerValue2 ,二次断电端口1 as TwoPowerOffPort1,二次断电端口2 as TwoPowerOffPort2 ,安装时间 as InstallTime,删除时间 as DeleteTime1";
            cmdstr += " ,是否分级报警 as IsGradingAlarm,分级上限1 as HighClassify1 ,分级上限2 as HighClassify2, 分级上限3 as HighClassify3,分级上限4 as HighClassify4 , 复电值 as RePowerValue ,是否多路控制 as IsMultiControl, 断电控制端口 as PowerOffPort,断电区域 as PowerArea,关联传感器 as RelateSensor ";
            cmdstr += ", 摄像头IP as CameraIP, 摄像头用户名 as CameraUser, 摄像头密码 as CameraPass from 端口参数表 ";
            cmdstr += " where 1=1";
            if (onlyIsDelete == true)
                cmdstr += " and 是否已删除=1";

            if (onlyIsValid == true)
                cmdstr += " and 是否有效=1";

            if(sensorType!="")
                cmdstr += " and 传感器类型='"+ sensorType+"'";
            cmdstr += " order by 分站地址,系统序号";

            //List<PortPara> list = db.DataListWorker<PortPara>(cmdstr);
            List<PortPara> list = remoteDB.DataListWorker<PortPara>(cmdstr);
            return list;
        }
        public void UpdateCaliberateTime(int SensorId, DateTime date)
        {
            string cmd = "update 端口参数表 set 上次标校时间='"+date+"' where 系统序号="+SensorId.ToString();
            localDB.SqlExecute(cmd);
        }
        public List<string> GetPortParaList(string brachId)
        {
           //string  cmdstr = "  select  cast(系统序号 as varchar)  + 'x' + cast(端口序号 as varchar) +  '-' + 传感器名称 + '-', CASE    WHEN 是否已删除=1 THEN '已删除'      ELSE '未删除'   END , ':', CASE   WHEN 是否有效=1 THEN  '有效'      ELSE '无效'    END ,'-', 安装地点 from 端口参数表  where 分站地址 = " + brachId + " order by 端口序号";
           string cmdstr = " select cast(系统序号 as varchar)  +'-' + cast(端口序号 as varchar) + '-' + 传感器名称 + '-' + (CASE    WHEN 是否已删除 = 1 THEN '已删除'      ELSE '未删除'   END ) +':' + (CASE   WHEN 是否有效 = 1 THEN  '有效'      ELSE '无效'    END )+'-' + 安装地点 from 端口参数表  where 分站地址 =" + brachId + " order by 端口序号";
            Console.WriteLine(cmdstr);
            List<string> list = localDB.DataListWorker<string>(cmdstr);
            return list;
        }
        public List<PortPara> GetPortParaByStationId(string stationId)
        {
            string cmdstr = "select 系统序号 as SensorId,是否已删除 as isDelete ,是否有效 as isValid,传感器名称 as SensorName ,传感器类型 as SensorType,传感器规格型号 as SensorModel ,安装地点 as InstallLocation,地址类型 as AddrType";
            cmdstr += ",分站地址 as BranchAddr,端口序号 as PortId ,是否模拟量 as IsAnalog,停显示 as StopDisplay,开显示 as OpenDisplay,单位 as Unit ,系数  as coefficient ,基值 as BaseValue ,是否报警 as isWarning";
            cmdstr += ",报警方式 as WarningType,报警上限值 as HighValue ,报警上限解除值 as HighRemoveValue,报警下限值 as LowValue,报警下限解除值 as LowRemoveValue ,是否断电控制 as isPowerControl ,是否终端断电  as isTerminal";
            cmdstr += " ,断电方式 as PowerMethod ,异常值判定比率 as AbnormalRatio,异常值判定间隔 as IntervalOfOutliers,量程下限 as LowLimitRange,量程上限 as HighLimitRange,断电值 as PowerValue,设备编号 as EquipmentId";
            cmdstr += ",显示方式 as DisplayMethod ,分级限时1 as ClassifyTime1,分级限时2 as ClassifyTime2,分级限时3 as ClassifyTime3,分级限时4 as ClassifyTime4,分级下限1 as LowClassify1,分级下限2 as LowClassify2,分级下限3 as LowClassify3,分级下限4 as LowClassify4";
            cmdstr += " ,上次标校时间 as LastTime1,安装地点编号 as InstallLocationId, 忽略故障 as IsIgnore, 工作面类型 as WorkType,甲烷传感器编号 as GasSensorId,标校周期 as MarkingCycle,截面积 as CrossSection,断电值2 as PowerValue2 ,二次断电端口1 as TwoPowerOffPort1,二次断电端口2 as TwoPowerOffPort2 ,安装时间 as InstallTime,删除时间 as DeleteTime1";
            cmdstr += " ,是否已删除 as IsDelete, 是否有效 as IsValid ,是否分级报警 as IsGradingAlarm,分级上限1 as HighClassify1 ,分级上限2 as HighClassify2, 分级上限3 as HighClassify3,分级上限4 as HighClassify4 , 复电值 as RePowerValue ,是否多路控制 as IsMultiControl, 断电控制端口 as PowerOffPort,断电区域 as PowerArea,关联传感器 as RelateSensor";
            cmdstr += ", 摄像头IP as CameraIP, 摄像头用户名 as CameraUser, 摄像头密码 as CameraPass from 端口参数表 where 分站地址 =" + stationId;
            //List<PortPara> portPara = db.DataListWorker<PortPara>(cmdstr);
            List<PortPara> portPara = remoteDB.DataListWorker<PortPara>(cmdstr);
            return portPara;
        }
        public PortPara GetPortParaBySensorId(string SensorId)
        {
            string cmdstr = "select 系统序号 as SensorId,是否已删除 as isDelete ,是否有效 as isValid,传感器名称 as SensorName ,传感器类型 as SensorType,传感器规格型号 as SensorModel ,安装地点 as InstallLocation,地址类型 as AddrType";
            cmdstr += ",分站地址 as BranchAddr,端口序号 as PortId ,是否模拟量 as IsAnalog,停显示 as StopDisplay,开显示 as OpenDisplay,单位 as Unit ,系数  as coefficient ,基值 as BaseValue ,是否报警 as isWarning";
            cmdstr += ",报警方式 as WarningType,报警上限值 as HighValue ,报警上限解除值 as HighRemoveValue,报警下限值 as LowValue,报警下限解除值 as LowRemoveValue ,是否断电控制 as isPowerControl ,是否终端断电  as isTerminal";
            cmdstr += " ,断电方式 as PowerMethod ,异常值判定比率 as AbnormalRatio,异常值判定间隔 as IntervalOfOutliers,量程下限 as LowLimitRange,量程上限 as HighLimitRange,断电值 as PowerValue,设备编号 as EquipmentId";
            cmdstr += " ,显示方式 as DisplayMethod,分级限时1 as ClassifyTime1,分级限时2 as ClassifyTime2,分级限时3 as ClassifyTime3,分级限时4 as ClassifyTime4,分级下限1 as LowClassify1,分级下限2 as LowClassify2,分级下限3 as LowClassify3,分级下限4 as LowClassify4";
            cmdstr += " ,上次标校时间 as LastTime1,安装地点编号 as InstallLocationId, 忽略故障 as IsIgnore, 工作面类型 as WorkType,甲烷传感器编号 as GasSensorId,标校周期 as MarkingCycle,截面积 as CrossSection,断电值2 as PowerValue2 ,二次断电端口1 as TwoPowerOffPort1,二次断电端口2 as TwoPowerOffPort2 ,安装时间 as InstallTime,删除时间 as DeleteTime1";
            cmdstr += " ,是否已删除 as IsDelete, 是否有效 as IsValid ,是否分级报警 as IsGradingAlarm,分级上限1 as HighClassify1 ,分级上限2 as HighClassify2, 分级上限3 as HighClassify3,分级上限4 as HighClassify4 , 复电值 as RePowerValue ,是否多路控制 as IsMultiControl, 断电控制端口 as PowerOffPort,断电区域 as PowerArea,关联传感器 as RelateSensor";
            cmdstr += " , 摄像头IP as CameraIp, 摄像头用户名 as CameraUser, 摄像头密码 as CameraPass from 端口参数表 where 系统序号 =" + SensorId;
            //PortPara portPara = db.GetSingle<PortPara>(cmdstr);
            PortPara portPara = remoteDB.GetSingle<PortPara>(cmdstr);
            return portPara;
        }

        public PortPara GetPortParaByPortId(string Brachaddr, string PortId,int isAnalog)
        {
            string cmdstr = "select 系统序号 as SensorId,是否已删除 as isDelete ,是否有效 as isValid,传感器名称 as SensorName ,传感器类型 as SensorType,传感器规格型号 as SensorModel ,安装地点 as InstallLocation";
            cmdstr += ",分站地址 as BranchAddr,端口序号 as PortId ,是否模拟量 as IsAnalog,停显示 as StopDisplay,开显示 as OpenDisplay,单位 as Unit ,系数  as coefficient ,基值 as BaseValue ,是否报警 as isWarning";
            cmdstr += ",报警方式 as WarningType,报警上限值 as HighValue ,报警上限解除值 as HighRemoveValue,报警下限值 as LowValue,报警下限解除值 as LowRemoveValue ,是否断电控制 as isPowerControl ,是否终端断电  as isTerminal";
            cmdstr += " ,断电方式 as PowerMethod ,异常值判定比率 as AbnormalRatio,异常值判定间隔 as IntervalOfOutliers,量程下限 as LowLimitRange,量程上限 as HighLimitRange,断电值 as PowerValue,设备编号 as EquipmentId";
            cmdstr += " ,显示方式 as DisplayMethod,分级限时1 as ClassifyTime1,分级限时2 as ClassifyTime2,分级限时3 as ClassifyTime3,分级限时4 as ClassifyTime4,分级下限1 as LowClassify1,分级下限2 as LowClassify2,分级下限3 as LowClassify3,分级下限4 as LowClassify4";
            cmdstr += " ,上次标校时间 as LastTime1,安装地点编号 as InstallLocationId, 忽略故障 as IsIgnore, 工作面类型 as WorkType,甲烷传感器编号 as GasSensorId,标校周期 as MarkingCycle,截面积 as CrossSection,断电值2 as PowerValue2 ,二次断电端口1 as TwoPowerOffPort1,二次断电端口2 as TwoPowerOffPort2 ,安装时间 as InstallTime,删除时间 as DeleteTime1";
            cmdstr += " ,是否已删除 as IsDelete, 是否有效 as IsValid ,是否分级报警 as IsGradingAlarm,分级上限1 as HighClassify1 ,分级上限2 as HighClassify2, 分级上限3 as HighClassify3,分级上限4 as HighClassify4 , 复电值 as RePowerValue ,是否多路控制 as IsMultiControl, 断电控制端口 as PowerOffPort,断电区域 as PowerArea,关联传感器 as RelateSensor";
            cmdstr += " , 摄像头IP as CameraIP, 摄像头用户名 as CameraUser, 摄像头密码 as CameraPass from 端口参数表 where 分站地址 =" + Brachaddr;
            cmdstr += " and 是否模拟量="+ isAnalog +" and 端口序号 = " + PortId;
            cmdstr += " and 是否已删除 = 0";
            cmdstr += " and 是否有效 = 1";
            //PortPara portPara = db.GetSingle<PortPara>(cmdstr);
            PortPara portPara = remoteDB.GetSingle<PortPara>(cmdstr);
            return portPara;
        }

        public List<PortPara> GetPortParaList2(bool onlyIsDelete, bool onlyIsValid, bool isAnalog = false)
        {
            string cmdstr = "select 系统序号 as SensorId,是否已删除 as isDelete ,是否有效 as isValid,传感器名称 as SensorName ,传感器类型 as SensorType,传感器规格型号 as SensorModel ,安装地点 as InstallLocation";
            cmdstr += ",分站地址 as BranchAddr,端口序号 as PortId ,是否模拟量 as IsAnalog,停显示 as StopDisplay,开显示 as OpenDisplay,单位 as Unit ,系数  as coefficient ,基值 as BaseValue ,是否报警 as isWarning";
            cmdstr += ",报警方式 as WarningType,报警上限值 as HighValue ,报警上限解除值 as HighRemoveValue,报警下限值 as LowValue,报警下限解除值 as LowRemoveValue ,是否断电控制 as isPowerControl ,是否终端断电  as isTerminal";
            cmdstr += " ,断电方式 as PowerMethod ,异常值判定比率 as AbnormalRatio,异常值判定间隔 as IntervalOfOutliers,量程下限 as LowLimitRange,量程上限 as HighLimitRange,断电值 as PowerValue,设备编号 as EquipmentId";
            cmdstr += " ,显示方式 as DisplayMethod ,分级限时1 as ClassifyTime1,分级限时2 as ClassifyTime2,分级限时3 as ClassifyTime3,分级限时4 as ClassifyTime4,分级下限1 as LowClassify1,分级下限2 as LowClassify2,分级下限3 as LowClassify3,分级下限4 as LowClassify4";
            cmdstr += " ,上次标校时间 as LastTime1,安装地点编号 as InstallLocationId, 忽略故障 as IsIgnore, 工作面类型 as WorkType,甲烷传感器编号 as GasSensorId,标校周期 as MarkingCycle,截面积 as CrossSection,断电值2 as PowerValue2 ,二次断电端口1 as TwoPowerOffPort1,二次断电端口2 as TwoPowerOffPort2 ,安装时间 as InstallTime,删除时间 as DeleteTime1";
            cmdstr += " ,是否分级报警 as IsGradingAlarm,分级上限1 as HighClassify1 ,分级上限2 as HighClassify2, 分级上限3 as HighClassify3,分级上限4 as HighClassify4 , 复电值 as RePowerValue ,是否多路控制 as IsMultiControl, 断电控制端口 as PowerOffPort,断电区域 as PowerArea ";
            cmdstr += " from 端口参数表 where 1=1";

            if (onlyIsDelete == true)
                cmdstr += " and 是否已删除=1";
            else
                cmdstr += " and 是否已删除=0"; 
            if (onlyIsValid == true)
                cmdstr += " and 是否有效=1";
            else
                cmdstr += " and 是否有效=0";
            if (isAnalog == true)
                cmdstr += " and 是否模拟量=1";
            cmdstr += " order by 分站地址,端口序号";

            //List<PortPara> list = db.DataListWorker<PortPara>(cmdstr);

            List<PortPara> list = remoteDB.DataListWorker<PortPara>(cmdstr);
            return list;
        }
        //+ "','" + portPara.HighClassify232
        public int AddPortPara(PortPara portPara,bool Check=true)
        {
            if (Check == true)
            {
                PortPara IsExistPortPara = GetPortParaBySensorId(portPara.SensorId.ToString());
                if (IsExistPortPara != null) // 该传感器已存在
                {
                    return 0;
                }
            }
            string cmdstr = "insert into 端口参数表 ([系统序号],[是否已删除],[是否有效],[传感器名称],[传感器类型],[传感器规格型号],[安装地点] ,[分站地址] ,[端口序号],[是否模拟量] ,[停显示],[开显示] ,[单位] ,[系数],[基值],[是否报警],[报警方式] ,[报警上限值],[报警上限解除值] ,[报警下限值],[报警下限解除值] ,[是否断电控制],[是否终端断电] ,[断电方式],[断电值] ,[复电值] ,[是否多路控制] ,[断电控制端口],[是否同步更新] ,[上传否] ,[上传否2] ,[异常值判定比率] ,[异常值判定间隔] ,[量程下限],[量程上限] ,[显示方式],[断电区域],[上传屏蔽否],[上传屏蔽否2],[上传屏蔽否3],[上传屏蔽否4],[是否分级报警] ,[分级联动字串] ,[分级上限1],[分级上限2] ,[分级上限3],[分级上限4] ,[分级限时1] ,[分级限时2],[分级限时3],[分级限时4],[分级下限1] ,[分级下限2],[分级下限3] ,[分级下限4],[是否应急联动],[上次标校时间],[工作面类型] ,[地址类型] ,[甲烷传感器编号] ,[安装地点编号],[标校周期],[矿井编号] ,[截面积] ,[忽略故障],[断电值2] ,[安装时间],[删除时间],[二次断电端口1] ,[二次断电端口2] ,[更新标记] ,[设备编号] ,[是否报表添加] ,[关联传感器],[摄像头IP],[摄像头用户名],[摄像头密码])";
            cmdstr += "values('" + portPara.SensorId + "', '" + portPara.IsDelete + "', '" + portPara.IsValid + "', '" + (portPara.SensorName == null ? @"null" : portPara.SensorName) + "', '" + portPara.SensorType + "', '" + portPara.SensorModel + "', '" + portPara.InstallLocation + "', '" + portPara.BranchAddr + "', '" + portPara.PortId + "', '" + portPara.IsAnalog + "', '" + portPara.StopDisplay + "', '" + portPara.OpenDisplay + "', '" + portPara.Unit + "', " + (portPara.Coefficient == null ? @"null" : portPara.Coefficient);
            cmdstr += "," + (portPara.BaseValue == null ? @"null" : portPara.BaseValue) + ",'" + portPara.IsWarning + "','" + portPara.WarningType + "'," + (portPara.HighValue == null ? @"null" : portPara.HighValue) + "," + (portPara.HighRemoveValue == null ? @"null" : portPara.HighRemoveValue);
            cmdstr += "," + (portPara.LowValue == null ? @"null" : portPara.LowValue) + "," + (portPara.LowRemoveValue == null ? @"null" : portPara.LowRemoveValue) + ",'" + portPara.IsPowerControl + "','" + portPara.IsTerminal + "','" + portPara.PowerMethod + "'," + (portPara.PowerValue == null ? @"null" : portPara.PowerValue) + "," + (portPara.RePowerValue == null ? @"null" : portPara.RePowerValue);
            cmdstr += ",'" + portPara.IsMultiControl + "','" + portPara.PowerOffPort + "','" + portPara.IsSynchronousUpdate + "','" + portPara.IsUpload + "','" + portPara.IsUpload2 + "'," + (portPara.AbnormalRatio == null ? @"null" : portPara.AbnormalRatio) + "," + (portPara.IntervalOfOutliers == null ? 0 : portPara.IntervalOfOutliers) + "," + (portPara.LowLimitRange == null ? @"null" : portPara.LowLimitRange) + "," + (portPara.HighLimitRange == null ? @"null" : portPara.HighLimitRange);
            cmdstr += ",'" + portPara.DisplayMethod + "','" + portPara.PowerArea + "','" + portPara.IsShielding1 + "','" + portPara.IsShielding2 + "','" + portPara.IsShielding3 + "','" + portPara.IsShielding4 + "','" + portPara.IsGradingAlarm + "','" + portPara.ClassifyLink + "'," + (portPara.HighClassify1 == null ? @"null" : portPara.HighClassify1) + "," + (portPara.HighClassify2 == null ? @"null" : portPara.HighClassify2) + "," + (portPara.HighClassify3 == null ? @"null" : portPara.HighClassify3) + "," + (portPara.HighClassify4 == null ? @"null" : portPara.HighClassify4);
            cmdstr += "," + (portPara.ClassifyTime1 == null ? @"null" : "'" + portPara.ClassifyTime1 + "'") + "," + (portPara.ClassifyTime2 == null ? @"null" : "'" + portPara.ClassifyTime2 + "'") + "," + (portPara.ClassifyTime3 == null ? @"null" : "'" + portPara.ClassifyTime3 + "'") + "," + (portPara.ClassifyTime4 == null ? @"null" : "'" + portPara.ClassifyTime4 + "'") + "," + (portPara.LowClassify1 == null ? @"null" : portPara.LowClassify1) + "," + (portPara.LowClassify2 == null ? @"null" : portPara.LowClassify2) + "," + (portPara.LowClassify3 == null ? @"null" : portPara.LowClassify3) + "," + (portPara.LowClassify4 == null ? @"null" : portPara.LowClassify4);
            cmdstr += ",'" + portPara.IsEmergencyLink + "'," + (portPara.LastTime1 == null ? @"null" : "'"+portPara.LastTime1+"'") + "," + (portPara.WorkType == null ? @"null" : portPara.WorkType) + "," + (portPara.AddrType == null ? @"null" : portPara.AddrType) + ",'" + portPara.GasSensorId + "','" + portPara.InstallLocationId + "'," + (portPara.MarkingCycle == null ? @"null" : portPara.MarkingCycle) + "," + (portPara.MineId == null ? @"null" : portPara.MineId) + ",'" + portPara.CrossSection + "'," + portPara.IsIgnore;
            cmdstr += "," + (portPara.PowerValue2 == null ? @"null" : portPara.PowerValue2) + "," + (portPara.InstallTime == null ? @"null" : "'"+portPara.InstallTime+"'") + "," + (portPara.DeleteTime1 == null ? @"null" : "'"+portPara.DeleteTime1+"'") + "," + (portPara.TwoPowerOffPort1 == null ? @"null" : portPara.TwoPowerOffPort1) + "," + (portPara.TwoPowerOffPort2 == null ? @"null" : portPara.TwoPowerOffPort2) + "," + (portPara.UpdateTag == null ? @"null" : portPara.UpdateTag) + ",'" + portPara.EquipmentId + "','" + portPara.IsReportAdd + "','" + portPara.RelateSensor + "','"+ portPara.CameraIp +"','"+portPara.CameraUser+"','"+portPara.CameraPass+"')";

            int i;
            if(Check)
                i= remoteDB.SqlExecute(cmdstr);
            else
                i = localDB.SqlExecute(cmdstr);
            if (!string.IsNullOrEmpty(portPara.RelateSensor)&& !string.IsNullOrEmpty(portPara.RelateSensor.Trim()))
            {
                cmdstr = "update 端口参数表 set 关联传感器='" + portPara.PortId + "' where 分站地址=" + portPara.BranchAddr + " and 端口序号=" + portPara.RelateSensor.Trim() + " and 是否模拟量=0";
                if (Check)
                    i = remoteDB.SqlExecute(cmdstr);
                else
                    i = localDB.SqlExecute(cmdstr);
            }
            LocalCache.Remove(PortConfigKey);
            return i;
        }

        public void UpdatePortPara(PortPara portPara)
        {
            string cmd = "update 端口参数表 set 是否已删除=" + portPara.IsDelete.ToString();
            cmd += ",系统序号=" + portPara.SensorId.ToString();
            cmd += ",是否有效=" + portPara.IsValid.ToString();
            cmd += ",传感器名称='" + portPara.SensorName.ToString() + "'";
            cmd += ",传感器类型=" + portPara.SensorType.ToString();
            cmd += ",安装地点='" + ((portPara.InstallLocation==null)?"": portPara.InstallLocation.ToString()) + "'";
            cmd += ",分站地址=" + portPara.BranchAddr.ToString();
            cmd += ",端口序号=" + portPara.PortId.ToString();
            cmd += ",是否模拟量=" + portPara.IsAnalog.ToString();
            cmd += ",停显示='" + ((portPara.StopDisplay==null)?"":portPara.StopDisplay.ToString()) + "'";
            cmd += ",开显示='" + ((portPara.OpenDisplay==null)?"":portPara.OpenDisplay.ToString()) + "'";
            cmd += ",单位='" + ((portPara.Unit==null)?"": portPara.Unit.ToString()) + "'";
            cmd += ",系数=" + (portPara.Coefficient == null ? @"null" : portPara.Coefficient);
            cmd += ",基值=" + (portPara.BaseValue == null ? @"null" : portPara.BaseValue) ;
            cmd += ",是否报警=" + portPara.IsWarning.ToString();
            cmd += ",报警方式=" + portPara.WarningType.ToString();
            cmd += ",报警上限值=" + (portPara.HighValue == null ? @"null" : portPara.HighValue) ;
            cmd += ",报警上限解除值=" + (portPara.HighRemoveValue == null ? @"null" : portPara.HighRemoveValue) ;
            cmd += ",报警下限值=" + (portPara.LowValue == null ? @"null" : portPara.LowValue);
            cmd += ",报警下限解除值=" + (portPara.LowRemoveValue == null ? @"null" : portPara.LowRemoveValue);
            cmd += ",是否断电控制=" + portPara.IsPowerControl.ToString();
            cmd += ",是否终端断电=" + portPara.IsTerminal.ToString();
            cmd += ",断电方式=" + portPara.PowerMethod.ToString();
            cmd += ",断电值=" + (portPara.PowerValue == null ? @"null" : portPara.PowerValue);
            cmd += ",复电值=" + (portPara.RePowerValue == null ? @"null" : portPara.RePowerValue);
            cmd += ",是否多路控制=" + portPara.IsMultiControl.ToString();
            cmd += ",断电控制端口='" + portPara.PowerOffPort + "'"; //.ToString()
            cmd += ",异常值判定比率=" + (portPara.AbnormalRatio == null ? @"null" : portPara.AbnormalRatio) ;
            cmd += ",异常值判定间隔=" + (portPara.IntervalOfOutliers == null ? @"null" : portPara.IntervalOfOutliers);
            cmd += ",量程下限=" + (portPara.LowLimitRange == null ? @"null" : portPara.LowLimitRange) ;
            cmd += ",量程上限=" + (portPara.HighLimitRange == null ? @"null" : portPara.HighLimitRange);
            cmd += ",断电区域='" + portPara.PowerArea + "'";
            cmd += ",是否分级报警=" + portPara.IsGradingAlarm.ToString();
            cmd += ",显示方式=" + portPara.DisplayMethod.ToString();
            cmd += ",分级上限1=" + (portPara.HighClassify1 == null ? @"null" : portPara.HighClassify1) ;
            cmd += ",分级上限2=" + (portPara.HighClassify2 == null ? @"null" : portPara.HighClassify2) ;
            cmd += ",分级上限3=" + (portPara.HighClassify3 == null ? @"null" : portPara.HighClassify3);
            cmd += ",分级上限4=" + (portPara.HighClassify4 == null ? @"null" : portPara.HighClassify4);
            cmd += ",分级限时1=" + (portPara.ClassifyTime1 == null ? @"null" : portPara.ClassifyTime1);
            cmd += ",分级限时2=" + (portPara.ClassifyTime2 == null ? @"null" : portPara.ClassifyTime2);
            cmd += ",分级限时3=" + (portPara.ClassifyTime3 == null ? @"null" : portPara.ClassifyTime3);
            cmd += ",分级限时4=" + (portPara.ClassifyTime4 == null ? @"null" : portPara.ClassifyTime4);
            cmd += ",分级下限1=" + (portPara.LowClassify1 == null ? @"null" : portPara.LowClassify1) ;
            cmd += ",分级下限2=" + (portPara.LowClassify2 == null ? @"null" : portPara.LowClassify2) ;
            cmd += ",分级下限3=" + (portPara.LowClassify3 == null ? @"null" : portPara.LowClassify3) ;
            cmd += ",分级下限4=" + (portPara.LowClassify4 == null ? @"null" : portPara.LowClassify4) ;
            cmd += ",上次标校时间='" + portPara.LastTime1 + "'";
            cmd += ",安装地点编号='" + portPara.InstallLocationId + "'";
            cmd += ",忽略故障='" + portPara.IsIgnore + "'";
            cmd += ",工作面类型=" + (portPara.WorkType == null ? @"null" : portPara.WorkType);
            cmd += ",甲烷传感器编号='" + portPara.GasSensorId + "'";
            cmd += ",标校周期=" + (portPara.MarkingCycle == null ? @"null" : portPara.MarkingCycle) ;
            cmd += ",截面积=" + portPara.CrossSection.ToString();
            cmd += ",断电值2=" + (portPara.PowerValue2 == null ? @"null" : portPara.PowerValue2);
            cmd += ",二次断电端口1=" + (portPara.TwoPowerOffPort1 == null ? @"null" : portPara.TwoPowerOffPort1) ;
            cmd += ",二次断电端口2=" + (portPara.TwoPowerOffPort2 == null ? @"null" : portPara.TwoPowerOffPort2) ;
            cmd += ",安装时间=" + (portPara.InstallTime == null ? @"null" : "'"+ portPara.InstallTime + "'");
            cmd += ",删除时间=" + (portPara.DeleteTime1 == null ? @"null" : "'" + portPara.DeleteTime1 + "'");
            cmd += ",设备编号=" + (portPara.EquipmentId == null ? @"null" : "'" + portPara.EquipmentId + "'");
            cmd += ",地址类型=" + (portPara.AddrType == null ? @"null" : portPara.AddrType);
            cmd += ",关联传感器=" + (portPara.RelateSensor == null ? @"null" : "'" + portPara.RelateSensor.ToString() + "'");
            cmd += ", 摄像头IP='" + portPara.CameraIp + "'";
            cmd += ", 摄像头用户名='" + portPara.CameraUser + "'";
            cmd += ", 摄像头密码='" + portPara.CameraPass + "'";
            cmd += " where 系统序号=" + portPara.SensorId.ToString();
            //db.SqlExecute(cmd);
            remoteDB.SqlExecute(cmd);
            if (!string.IsNullOrEmpty(portPara.RelateSensor))
            {
                cmd = "update 端口参数表 set 关联传感器='" + portPara.PortId + "' where 分站地址=" + portPara.BranchAddr + " and 端口序号=" + portPara.RelateSensor + " and 是否模拟量=0";
                //db.SqlExecute(cmd);
                remoteDB.SqlExecute(cmd);
            }
            LocalCache.Remove(PortConfigKey);

        }
        public int DeletePortPara(int SensorId)
        {
            string cmd = "update 端口参数表 set 是否已删除=" + 1.ToString();
            cmd += " where 系统序号=" + SensorId.ToString();
            //int success = db.SqlExecute(cmd);
            int success = remoteDB.SqlExecute(cmd);
            LocalCache.Remove(PortConfigKey);
            return success;
        }

        public PortPara GetDefaultPara(string sensorType)
        {

            string cmdstr = "select 系统序号 as SensorId,是否已删除 as isDelete ,是否有效 as isValid,传感器名称 as SensorName ,传感器类型 as SensorType,传感器规格型号 as SensorModel ,安装地点 as InstallLocation";
            cmdstr += ",分站地址 as BranchAddr,端口序号 as PortId ,是否模拟量 as IsAnalog,停显示 as StopDisplay,开显示 as OpenDisplay,单位 as Unit ,系数  as coefficient ,基值 as BaseValue ,是否报警 as isWarning";
            cmdstr += ",报警方式 as WarningType,报警上限值 as HighValue ,报警上限解除值 as HighRemoveValue,报警下限值 as LowValue,报警下限解除值 as LowRemoveValue ,是否断电控制 as isPowerControl ,是否终端断电  as isTerminal";
            cmdstr += " ,断电方式 as PowerMethod ,异常值判定比率 as AbnormalRatio,异常值判定间隔 as IntervalOfOutliers,量程下限 as LowLimitRange,量程上限 as HighLimitRange,断电值 as PowerValue,设备编号 as EquipmentId";
            cmdstr += " ,分级限时1 as ClassifyTime1,分级限时2 as ClassifyTime2,分级限时3 as ClassifyTime3,分级限时4 as ClassifyTime4,分级下限1 as LowClassify1,分级下限2 as LowClassify2,分级下限3 as LowClassify3,分级下限4 as LowClassify4";
            cmdstr += " ,上次标校时间 as LastTime1,安装地点编号 as InstallLocationId, 忽略故障 as IsIgnore, 工作面类型 as WorkType,甲烷传感器编号 as GasSensorId,标校周期 as MarkingCycle,截面积 as CrossSection,断电值2 as PowerValue2 ,二次断电端口1 as TwoPowerOffPort1,二次断电端口2 as TwoPowerOffPort2 ,安装时间 as InstallTime,删除时间 as DeleteTime1";
            cmdstr += " ,是否已删除 as IsDelete, 是否有效 as IsValid ,是否分级报警 as IsGradingAlarm,分级上限1 as HighClassify1 ,分级上限2 as HighClassify2, 分级上限3 as HighClassify3,分级上限4 as HighClassify4 , 复电值 as RePowerValue ,是否多路控制 as IsMultiControl, 断电控制端口 as PowerOffPort,断电区域 as PowerArea,关联传感器 as RelateSensor, 摄像头IP as CameraIP, 摄像头用户名 as CameraUser, 摄像头密码 as CameraPass from 端口参数表 ";
            cmdstr += " where  传感器类型= " + sensorType;

            //PortPara portPara = db.DataListWorker<PortPara>(cmdstr).FirstOrDefault();
            PortPara portPara = remoteDB.DataListWorker<PortPara>(cmdstr).FirstOrDefault();
            return portPara;

        }

        public int UpdateOrAddPortPara(PortPara portPara)
        {
            var sensor = GetPortParaBySensorId(portPara.SensorId.ToString());
            if (sensor==null)
            {
                AddPortPara(portPara);
                return 1;
            }
            else
            {
                UpdatePortPara(portPara);
                return 0;
            }
        }

        #endregion

        /// <summary>
        /// 获取站点配置表   (查、插入)
        /// </summary>
        /// <returns></returns>
        #region 站点配置表
        public void SyncSiteConfig(List<SiteConfig> list)
        {
            if (localDB.Equals(remoteDB))
                return;
            string cmd = "delete from 站点配置表; ";
            localDB.SqlExecute(cmd);
            foreach (var v in list)
            {
                AddSiteConfig(v,false);
            }
        }
        public List<SiteConfig> GetSiteConfigList(bool IsValid=false)
        {
            string cmdstr="";
            cmdstr = "select 序号 as Id,有效否 as IsValid,分站地址 as BrachAdress,分站名称 as BrachName,安装地点 as InstallLocation";
            cmdstr += ",安装时间 as InstallTime,运行状态 as RunningState,掉电次数统计 as PowerOffCount,最近掉电时间 as LastPowerOffTime";
            cmdstr += ",分站上次整定时间 as BrachLastSetTime,当前整定端口序号 as CurSetPort,上传否 as IsUpload,上传否2 as IsUpload2";
            cmdstr += ",是否就地断电 as IsPowOffCur,分站参数整定否 as IsBrachParaSet,是否风电瓦斯闭锁 as IsInterlocked";
            cmdstr += ",开出端口名称 as OpenPortName,馈电关联监测 as Feedmonitoring,安装地点编号 as InstallId,屏蔽否 as IsShield";
            cmdstr += ",分站故障状态 as BrachfarlureState ,commid as Commid,是否存在历史数据 as IsHistoryData, 充放电周期 as ChargeDuration, 上次充放电时间 as LastChargeTime";
            cmdstr += ", 是否允许上传历史数据 as IsPermitUpload,分站IP as BrachIp,版本 as Version, case when [判定断线时间]=null then 5 when [判定断线时间]=0 then 5 else  [判定断线时间] end as OfflineDuration, 开停传感器 as SwitchSensors from 站点配置表 ";
            if (IsValid)
                cmdstr += " where 有效否=1";
            cmdstr +=" order by 分站地址";
            //List<SiteConfig> list = db.QueryCacheList<SiteConfig>(SiteConfigKey, cmdstr);
            List<SiteConfig> list = remoteDB.QueryCacheList<SiteConfig>(SiteConfigKey, cmdstr);
            return list;
        }
        public List<SiteConfig> GetSiteConfigListFromDB(bool showInvalid=false)
        {
            string cmdstr = "update 站点配置表 set 判定断线时间=5 where 判定断线时间=5 or 判定断线时间=null; ";
            localDB.SqlExecute(cmdstr);
            cmdstr = "select 序号 as Id,有效否 as IsValid,分站地址 as BrachAdress,分站名称 as BrachName,安装地点 as InstallLocation";
            cmdstr += ",安装时间 as InstallTime,运行状态 as RunningState,掉电次数统计 as PowerOffCount,最近掉电时间 as LastPowerOffTime";
            cmdstr += ",分站上次整定时间 as BrachLastSetTime,当前整定端口序号 as CurSetPort,上传否 as IsUpload,上传否2 as IsUpload2";
            cmdstr += ",是否就地断电 as IsPowOffCur,分站参数整定否 as IsBrachParaSet,是否风电瓦斯闭锁 as IsInterlocked";
            cmdstr += ",开出端口名称 as OpenPortName,馈电关联监测 as Feedmonitoring,安装地点编号 as InstallId,屏蔽否 as IsShield";
            cmdstr += ",分站故障状态 as BrachfarlureState ,commid as Commid,是否存在历史数据 as IsHistoryData, 充放电周期 as ChargeDuration, 上次充放电时间 as LastChargeTime";
            cmdstr += ", 是否允许上传历史数据 as IsPermitUpload,分站IP as BrachIp,版本 as Version,判定断线时间 as OfflineDuration , 开停传感器 as SwitchSensors from 站点配置表 where  1=1";
            if (showInvalid == false)
                cmdstr += " and 有效否=1 ";
            cmdstr+=" order by 分站地址";
            //List<SiteConfig> list = db.QueryList<SiteConfig>( cmdstr);
            List<SiteConfig> list = remoteDB.QueryList<SiteConfig>(cmdstr);
            return list;
        }
        public SiteConfig GetSiteConfigById(string BrachAdress)
        {
            string cmdstr = "select 序号 as Id,有效否 as IsValid,分站地址 as BrachAdress,分站名称 as BrachName,安装地点 as InstallLocation";
            cmdstr += ",安装时间 as InstallTime,运行状态 as RunningState,掉电次数统计 as PowerOffCount,最近掉电时间 as LastPowerOffTime";
            cmdstr += ",分站上次整定时间 as BrachLastSetTime,当前整定端口序号 as CurSetPort,上传否 as IsUpload,上传否2 as IsUpload2";
            cmdstr += ",是否就地断电 as IsPowOffCur,分站参数整定否 as IsBrachParaSet,是否风电瓦斯闭锁 as IsInterlocked";
            cmdstr += ",开出端口名称 as OpenPortName,馈电关联监测 as Feedmonitoring,安装地点编号 as InstallId,屏蔽否 as IsShield";
            cmdstr += ",分站故障状态 as BrachfarlureState ,commid as Commid,是否存在历史数据 as IsHistoryData, 充放电周期 as ChargeDuration, 上次充放电时间 as LastChargeTime";
            cmdstr += ", 是否允许上传历史数据 as IsPermitUpload,分站IP as BrachIp,版本 as Version,判定断线时间 as OfflineDuration, 开停传感器 as SwitchSensors from 站点配置表 where 分站地址 =" + BrachAdress;
            //SiteConfig siteConfig = db.GetSingle<SiteConfig>(cmdstr);
            SiteConfig siteConfig = remoteDB.GetSingle<SiteConfig>(cmdstr);
            return siteConfig;
        }
        public SiteConfig GetSiteConfigBySysId(string id)
        {
            string cmdstr = "select 序号 as Id,有效否 as IsValid,分站地址 as BrachAdress,分站名称 as BrachName,安装地点 as InstallLocation";
            cmdstr += ",安装时间 as InstallTime,运行状态 as RunningState,掉电次数统计 as PowerOffCount,最近掉电时间 as LastPowerOffTime";
            cmdstr += ",分站上次整定时间 as BrachLastSetTime,当前整定端口序号 as CurSetPort,上传否 as IsUpload,上传否2 as IsUpload2";
            cmdstr += ",是否就地断电 as IsPowOffCur,分站参数整定否 as IsBrachParaSet,是否风电瓦斯闭锁 as IsInterlocked";
            cmdstr += ",开出端口名称 as OpenPortName,馈电关联监测 as Feedmonitoring,安装地点编号 as InstallId,屏蔽否 as IsShield";
            cmdstr += ",分站故障状态 as BrachfarlureState ,commid as Commid,是否存在历史数据 as IsHistoryData, 充放电周期 as ChargeDuration, 上次充放电时间 as LastChargeTime";
            cmdstr += ", 是否允许上传历史数据 as IsPermitUpload,分站IP as BrachIp,版本 as Version,判定断线时间 as OfflineDuration, 开停传感器 as SwitchSensors from 站点配置表 where 序号 =" + id;
            //SiteConfig siteConfig = db.GetSingle<SiteConfig>(cmdstr);
            SiteConfig siteConfig = remoteDB.GetSingle<SiteConfig>(cmdstr);
            return siteConfig;
        }

        //+ "','" + siteConfig.MaxValue
        public int AddSiteConfig(SiteConfig siteConfig,bool check=true)
        {
            if (check == true)
            {
                SiteConfig IsExistSiteConfig = GetSiteConfigById(siteConfig.BrachAdress.ToString());
                if (IsExistSiteConfig != null) // 该分站已存在
                {
                    return 0;
                }
            }
            string cmdstr =  "insert into 站点配置表  ([序号] ,[有效否] ,[分站地址] ,[分站名称] ,[安装地点],[安装时间],[运行状态] ,[掉电次数统计],[最近掉电时间] ,[分站故障状态],[分站上次整定时间],[当前整定端口序号] ,[上传否],[上传否2],[是否就地断电] ,[分站参数整定否] ,[是否风电瓦斯闭锁],[开出端口名称],[馈电关联监测],[安装地点编号] ,[屏蔽否] ,[commid] ,[是否存在历史数据],[是否允许上传历史数据]\r\n ,[分站IP]\r\n ,[版本]\r\n ,[维检状况]\r\n ,[判定断线时间],充放电周期 , 上次充放电时间 ,开停传感器 ) values('" + siteConfig.Id.ToString() + "','" + siteConfig.IsValid + "','" + siteConfig.BrachAdress + "','" + siteConfig.BrachName + "','" + siteConfig.InstallLocation + "','" + siteConfig.InstallTime + "','" + (string.IsNullOrEmpty(siteConfig.RunningState) ? "断线" : siteConfig.RunningState) + "','" + siteConfig.PowerOffCount + "','" + siteConfig.LastPowerOffTime + "','" + siteConfig.BrachfarlureState + "','" + siteConfig.BrachLastSetTime + "','" + siteConfig.CurSetPort + "','" + siteConfig.IsUpload + "','" + siteConfig.IsUpload2 + "','" + siteConfig.IsPowOffCur + "','" + siteConfig.IsBrachParaSet + "','" + siteConfig.IsInterlocked + "','" + siteConfig.OpenPortName + "','" + siteConfig.Feedmonitoring + "','" + siteConfig.InstallId + "','" + siteConfig.IsShield + "','" + siteConfig.Commid + "','" + siteConfig.IsHistoryData + "','" + siteConfig.IsPermitUpload + "','" + siteConfig.BrachIp + "','" + siteConfig.Version + "','" + siteConfig.MaintainedStatus + "','" + siteConfig.OfflineDuration + "'," + siteConfig.ChargeDuration + ",'','"+siteConfig.SwitchSensors+"')";
            int i;
            if(check)
                i=localDB.SqlExecute(cmdstr);
            else
                i=localDB.SqlExecute(cmdstr);
            //IsExistSiteConfig = GetSiteConfigById(siteConfig.BrachAdress.ToString());
            List<SiteConfig> siteConfigList = LocalCache.Get<List<SiteConfig>>(SiteConfigKey);
            //siteConfigList.Add(IsExistSiteConfig);
            LocalCache.Set(siteConfigList, SiteConfigKey);
            return i;
        }
        public void DeleteSiteConfig(int BrachAdress)
        {
           //string cmd = "update 站点配置表 set 有效否=0 where 分站地址=" + BrachAdress.ToString();
            string cmd = "delete from 站点配置表 where 分站地址=" + BrachAdress.ToString();
            //db.SqlExecute(cmd);
            localDB.SqlExecute(cmd);
            cmd = "update 端口参数表 set 是否已删除=1 where 分站地址="+BrachAdress;
            //db.SqlExecute(cmd);
            localDB.SqlExecute(cmd);
            cmd = "delete from 即时数据表 where 分站地址=" + BrachAdress;
            //db.SqlExecute(cmd);
            localDB.SqlExecute(cmd);
            LocalCache.Remove(SiteConfigKey);
            LocalCache.Remove(PortConfigKey);
            LocalCache.Remove(RealtimeDataKey);
        }
        public void UpdateSiteConfig(SiteConfig siteConfig, string id = "")
        {
            string cmd;
            if (siteConfig.OpenPortName.Trim('\b').Length == 0)
            {
                siteConfig.OpenPortName=";;;;;;;";
            }
            else if (siteConfig.OpenPortName.IndexOf(";;;;;;;") >= 0)
            {
                siteConfig.OpenPortName = siteConfig.OpenPortName.Trim(',');
            }
            else if (siteConfig.OpenPortName.IndexOf(';') >= 0)
            {
                siteConfig.OpenPortName = siteConfig.OpenPortName.Trim('\b');
            }
            else
            {
                siteConfig.OpenPortName = siteConfig.OpenPortName.Replace('\b', ';');
            }
            if (siteConfig.Feedmonitoring.Trim('\b').Length == 0)
            {
                siteConfig.Feedmonitoring = ";;;;;;;";
            }
            else if (siteConfig.Feedmonitoring.IndexOf(';') >= 0)
            {
                siteConfig.Feedmonitoring = siteConfig.Feedmonitoring.Trim('\b');
            }
            else
            {
                siteConfig.Feedmonitoring = siteConfig.Feedmonitoring.Replace('\b', ';');
            }
            if (!string.IsNullOrEmpty(id) )
            {
                SiteConfig site = GetSiteConfigById(id);
                if (site != null)
                {
                    cmd = "update 即时数据表 set 分站地址=" + siteConfig.BrachAdress + " where 系统序号 in (select 系统序号 from 端口参数表 where 分站地址=" + site.BrachAdress+")";
                    localDB.SqlExecute(cmd);
                    cmd = "update 端口参数表 set 分站地址=" + siteConfig.BrachAdress + " where 分站地址=" + site.BrachAdress;
                    localDB.SqlExecute(cmd);
                    cmd = "update 站点配置表 set 有效否=" + siteConfig.IsValid.ToString();
                    //cmd += ",序号=" + siteConfig.Id.ToString();
                    cmd += ",分站地址=" + siteConfig.BrachAdress.ToString();
                    cmd += ",分站名称='" + siteConfig.BrachName + "'";
                    cmd += ",安装地点='" + siteConfig.InstallLocation + "'";
                    cmd += ",运行状态='" + siteConfig.RunningState + "'";
                    cmd += ",掉电次数统计='" + siteConfig.PowerOffCount + "'";
                    cmd += ",当前整定端口序号='" + siteConfig.CurSetPort + "'";
                    cmd += ",上传否='" + siteConfig.IsUpload + "'";
                    cmd += ",上传否2='" + siteConfig.IsUpload2 + "'";
                    cmd += ",是否就地断电=" + siteConfig.IsPowOffCur.ToString();
                    cmd += ",分站参数整定否='" + siteConfig.IsBrachParaSet + "'";
                    cmd += ",是否风电瓦斯闭锁='" + siteConfig.IsInterlocked + "'";
                    cmd += ",开出端口名称='" + siteConfig.OpenPortName + "'";
                    cmd += ",馈电关联监测='" + siteConfig.Feedmonitoring + "'";
                    cmd += ",安装地点编号='" + siteConfig.InstallId + "'";
                    cmd += ",屏蔽否='" + siteConfig.IsShield + "'";
                    cmd += ",分站故障状态='" + siteConfig.BrachfarlureState + "'";
                    cmd += ",commid='" + siteConfig.Commid + "'";
                    cmd += ",是否存在历史数据='" + siteConfig.IsHistoryData + "'";
                    cmd += ",是否允许上传历史数据='" + siteConfig.IsPermitUpload + "'";
                    cmd += ",分站IP='" + siteConfig.BrachIp + "'";
                    cmd += ",版本='" + siteConfig.Version + "'";
                    cmd += ",安装时间='" + siteConfig.InstallTime + "'";
                    cmd += ",判定断线时间='" + siteConfig.OfflineDuration + "'";
                    cmd += ",充放电周期='" + siteConfig.ChargeDuration + "'";
                    cmd += ",开停传感器='" + siteConfig.SwitchSensors + "'";
                    if (siteConfig.LastCHargeTime!=null)
                        cmd += ",上次充放电时间='" + siteConfig.LastCHargeTime + "'";
                    cmd += " where 分站地址=" + id;
                    localDB.SqlExecute(cmd);
                }

            }
            else
            {
                cmd = "update 站点配置表 set 有效否=" + siteConfig.IsValid.ToString();
                //cmd += ",序号=" + siteConfig.Id.ToString();
                cmd += ",分站地址=" + siteConfig.BrachAdress.ToString();
                cmd += ",分站名称='" + siteConfig.BrachName + "'";
                cmd += ",安装地点='" + siteConfig.InstallLocation + "'";
                cmd += ",运行状态='" + siteConfig.RunningState + "'";
                cmd += ",掉电次数统计='" + siteConfig.PowerOffCount + "'";
                cmd += ",当前整定端口序号='" + siteConfig.CurSetPort + "'";
                cmd += ",上传否='" + siteConfig.IsUpload + "'";
                cmd += ",上传否2='" + siteConfig.IsUpload2 + "'";
                cmd += ",是否就地断电=" + siteConfig.IsPowOffCur.ToString();
                cmd += ",分站参数整定否='" + siteConfig.IsBrachParaSet + "'";
                cmd += ",是否风电瓦斯闭锁='" + siteConfig.IsInterlocked + "'";
                cmd += ",开出端口名称='" + siteConfig.OpenPortName + "'";
                cmd += ",馈电关联监测='" + siteConfig.Feedmonitoring + "'";
                cmd += ",安装地点编号='" + siteConfig.InstallId + "'";
                cmd += ",屏蔽否='" + siteConfig.IsShield + "'";
                cmd += ",分站故障状态='" + siteConfig.BrachfarlureState + "'";
                cmd += ",commid='" + siteConfig.Commid + "'";
                cmd += ",是否存在历史数据='" + siteConfig.IsHistoryData + "'";
                cmd += ",是否允许上传历史数据='" + siteConfig.IsPermitUpload + "'";
                cmd += ",分站IP='" + siteConfig.BrachIp + "'";
                cmd += ",版本='" + siteConfig.Version + "'";
                cmd += ",安装时间='" + siteConfig.InstallTime + "'";
                cmd += ",判定断线时间='" + siteConfig.OfflineDuration + "'";
                cmd += ",充放电周期='" + siteConfig.ChargeDuration + "'";
                cmd += ",上次充放电时间='" + siteConfig.LastCHargeTime + "'";
                cmd += ",开停传感器='" + siteConfig.SwitchSensors + "'";
                cmd += " where 分站地址=" + siteConfig.BrachAdress.ToString();
                localDB.SqlExecute(cmd);
            }
            LocalCache.Remove(SiteConfigKey);
            LocalCache.Remove(RealtimeDataKey);
            LocalCache.Remove(PortConfigKey);
        }
        public void UpdateLastTime(string BrachAdress)
        {
            string cmd = "update 站点配置表 set 分站上次整定时间='" + DateTime.Now + "'";
            cmd += " where 分站地址=" + BrachAdress.ToString();
            //db.SqlExecute(cmd);
            localDB.SqlExecute(cmd);
            LocalCache.Remove(SiteConfigKey);
        }
        public void UpdateLastChargeTime(string BrachAdress)
        {
            string cmd = "update 站点配置表 set 上次充放电时间='" + DateTime.Now + "'";
            cmd += " where 分站地址=" + BrachAdress.ToString();
            //db.SqlExecute(cmd);
            localDB.SqlExecute(cmd);
            LocalCache.Remove(SiteConfigKey);
        }
        public int  UpdateOrAddSiteConfig(SiteConfig siteConfig )
        {
            var s = GetSiteConfigById(siteConfig.BrachAdress.ToString());
            if (s==null)
            {
                siteConfig.RunningState = "断线";
                AddSiteConfig(siteConfig);
                return 0;
            }
            else
            {
                UpdateSiteConfig(siteConfig,siteConfig.BrachAdress.ToString());
                return s.BrachAdress;
            }
        }


        #endregion



        /// <summary>
        /// 获取开关数据表   (查、插入)
        /// </summary>
        /// <returns></returns>
        #region 开关数据表
        //wang
        public List<SwitchData> GetSwitchDataList(string tableDate)
        {
            string cmdstr = "select 系统序号 as SensorId,状态 as Status,时间 as Time,时间2 as Time2,数值 as Value,断电否 as IsPowerOff,断电信息 as PowerOffMsg,备注 as Describe,上传否 as IsUpload,上传否2 as IsUpload2,上传否3 as IsUpload3,上传否4 as IsUpload4  from 开关数据表" + tableDate;
            cmdstr += " order by 系统序号,时间";
            List<SwitchData> list = localDB.QueryList<SwitchData>(cmdstr);

            if (list == null)
            {
                cmdstr = "select 系统序号 as SensorId,状态 as Status,时间 as Time,,时间2 as Time2,数值 as Value,断电否 as IsPowerOff,断电信息 as PowerOffMsg,备注 as Describe,上传否 as IsUpload,上传否2 as IsUpload2,上传否3 as IsUpload3,上传否4 as IsUpload4  from 开关数据表" + tableDate;
                cmdstr += " order by 系统序号,时间";
                list = localDB.QueryList<SwitchData>(cmdstr);
            }
            return list;
        }
        //public List<SwitchData> GetSwitchDataList(string tableDate)
        //{
        //    string cmdstr = "select concat(分站地址 ,'-',端口序号,'/',开关数据表" + tableDate + ".系统序号,'/',传感器名称,'/',安装地点)as Cribe,状态 as Status,时间 as Time,数值 as Value,断电否 as IsPowerOff,断电信息 as PowerOffMsg,备注 as Describe,开关数据表" + tableDate + ".上传否 as IsUpload ,开关数据表" + tableDate + ".上传否2 as IsUpload2 ,开关数据表" + tableDate + ".上传否3 as IsUpload3 ,开关数据表" + tableDate + ".上传否4 as IsUpload4 from 开关数据表" + tableDate + " INNER JOIN 端口参数表 ON 端口参数表.系统序号 = 开关数据表" + tableDate + ".系统序号 order by 开关数据表" + tableDate + ".系统序号";
        //    List<SwitchData> list = db.QueryList<SwitchData>(cmdstr);
        //    return list;
        //}

        //wang
        public SwitchData GetSwitchDataById(string Id, string tableDate, DateTime? date)
        {
            string cmdstr = "select  系统序号 as SensorId,数值 as Value,状态 as Status,时间 as Time,开出端口号 as OpenPort ,断电否 as IsPowerOff,断电信息 as PowerOffMsg ,备注 as Describe,上传否 as IsUpload,上传否2 as IsUpload2,上传否3 as IsUpload3,上传否4 as IsUpload4   from 开关数据表" + tableDate;
            cmdstr += " where 系统序号=" + Id;
            if (date != null)
            {
                cmdstr += " and 时间='" + date + "'";
            }
            cmdstr += " order by 时间";
            SwitchData SwitchData = localDB.QueryList<SwitchData>(cmdstr).FirstOrDefault();
            return SwitchData;
        }
        //wang
        public void UpdateSwitchData(SwitchData switchData, string Id, string tableDate, DateTime? time)
        {
            string cmd = "update 开关数据表" + tableDate + " set 数值=" + switchData.Value.ToString();
            cmd += ",状态=" + switchData.Status.ToString();
            cmd += ",时间='" + switchData.Time + "'";
            cmd += ",开出端口号=" + switchData.OpenPort.ToString();
            cmd += ",断电否=" + switchData.IsPowerOff.ToString();
            cmd += ",断电信息='" + switchData.PowerOffMsg + "'";
            cmd += ",备注='" + switchData.Describe + "'";
            cmd += ",上传否=" + switchData.IsUpload;
            cmd += ",上传否2=" + switchData.IsUpload2;
            cmd += ",上传否3=" + switchData.IsUpload3;
            cmd += ",上传否4=" + switchData.IsUpload4;
            cmd += " where 系统序号=" + Id;
            if (time != null)
            {
                cmd += " and 时间='" + time + "'";
            }
            localDB.SqlExecute(cmd);
        }
        //+ "','" + offPower.MaxValue
        public int AddSwitchData(SwitchData switchData, string tableDate)
        {
            if (!IsExistTable("开关数据表" + tableDate))
                CreateSwtichRecordTable(tableDate);
            //string cmdstr = "insert into 开关数据表" + tableDate + " values('" + switchData.SensorId + "','" + switchData.Status + "'," + (switchData.Time == null ? @"null" : switchData.Time) + "," + (switchData.Time2 == null ? @"null" : switchData.Time2) + ",'"+ switchData.Value + "','" + switchData.OpenPort + "','" + switchData.IsPowerOff + "','" + switchData.PowerOffMsg + "','" + switchData.Describe + "','" + switchData.IsUpload + "','" + switchData.IsUpload2 + "','" + switchData.IsUpload3 + "','" + switchData.IsUpload4 + "');";
            string cmdstr = "insert into 开关数据表" + tableDate + " (系统序号,状态,时间,时间2,数值,开出端口号,断电否,断电信息,备注,上传否,上传否2,上传否3,上传否4) values('" + switchData.SensorId + "','" + switchData.Status + "'," + (switchData.Time == new DateTime(0) ? @"null" : "'"+ switchData.Time+"'") + "," + (switchData.Time2 == new DateTime(0) ? @"null" : "'"+switchData.Time2+"'") + ",'" + switchData.Value + "','" + switchData.OpenPort + "','" + switchData.IsPowerOff + "','" + switchData.PowerOffMsg + "','" + switchData.Describe + "','" + switchData.IsUpload + "','" + switchData.IsUpload2 + "','" + switchData.IsUpload3 + "','" + switchData.IsUpload4 + "');";
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }
        public int AddBatchSwitchData(List<Immediate> immediates, string tableDate, DateTime time, DateTime time2,bool isHistory)
        {
            if (!IsExistTable("开关数据表" + tableDate))
            {
                CreateSwtichRecordTable(tableDate);
                CreateHistoryDataTable(tableDate);
            }
                
            string cmdstr = "";
            int seconds;
            int status;
            int upload = isHistory ? 1 : 0;
            List<PortPara> sensors = GetPortParaList().FindAll(p => p.IsAnalog == 0);
            foreach (var v in immediates)
            {
                if (sensors.Find(p => p.SensorId == v.SensorId) != null)
                {
                    seconds =(int) (time - v.DateTime).TotalSeconds;
                    if (v.Status == 9)//开关故障
                        status = -2;
                    else
                        status = v.CountValue==null?0: (int)v.CountValue;
                    cmdstr += "insert into 开关数据表" + tableDate + " (系统序号,状态,时间,时间2,数值,开出端口号,断电否,断电信息,备注,上传否,上传否2,上传否3,上传否4) values('" + v.SensorId + "','" + status + "','" + time + "','" + time2 + "','" + seconds + "','','','','','"+upload+"','','','');\n";
                    //cmdstr += "insert into 开关数据表" + tableDate + " values('" + v.SensorId + "','" + status + "','" + time + "','"+ time2 + "','" + seconds + "','','','','','','','','');\n";
                     cmdstr += "insert into 历史数据表" + tableDate + " (系统序号,当前值,时间,时间2,上传否,上传否2,上传否3,上传否4,数据状态) values (" + v.SensorId + "," + status + ",'" + time + "','" +time2+ "','"+upload+"','','','','"+v.DataStatus+"');";

                }
            }
            if (cmdstr == string.Empty)
                return 0;
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }

        public void UpdateSwitchData(SwitchData switchData, string tableDate)
        {
            string cmd = "update 开关数据表" + tableDate + " set 状态=" + switchData.Status.ToString();
            cmd += ",时间=" + switchData.Time.ToString();
            cmd += ",Time='" + switchData.Time + "'";
            cmd += ",数值=" + switchData.Value.ToString();
            cmd += ",断电否=" + switchData.IsPowerOff.ToString();
            cmd += ",断电信息=" + switchData.PowerOffMsg.ToString();
            cmd += ",备注='" + switchData.Describe + "'";
            cmd += " where 系统序号=" + switchData.SensorId.ToString();
            localDB.SqlExecute(cmd);
        }
        public void CreateSwtichRecordTable(string yymmdd)
        {
            string cmdstr = "IF OBJECT_ID('开关数据表" + yymmdd + "','u') IS NULL BEGIN CREATE TABLE [dbo].[开关数据表" + yymmdd + "]( [系统序号] [int] NOT NULL, [状态] [int] NULL, [时间] [datetime] NULL, [时间2] [datetime] NULL, [数值] [int] NULL, [开出端口号] [smallint] NULL, [断电否] [bit] NULL, [断电信息] [nvarchar](50) NULL, [备注] [ntext] NULL, [上传否] [bit] NULL, [上传否2] [bit] NULL, [上传否3] [bit] NULL, [上传否4] [bit] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] SELECT 1 END ELSE SELECT 0";
            localDB.SqDDLlExecute(cmdstr);
        }
        public List<SwitchData> GetSwitchDataDuration(DateTime StartTable, DateTime EndTable, int SensorId)
        {
            DateTime date = StartTable;
            string cmdstr = "select 系统序号 as SensorId,状态 as Status,时间 as Time,数值 as Value,断电否 as IsPowerOff,断电信息 as PowerOffMsg,Convert(nvarchar,备注) as Describe  from 开关数据表" + date.ToString("yyyyMMdd").Substring(2) + " where 系统序号=" + SensorId.ToString() + " and 时间>='"+ StartTable+"' and 时间<='"+EndTable+"'";

            while (DateTime.Compare(date.Date, EndTable.Date) < 0)
            {
                date = date.AddDays(1);
                cmdstr += " union select 系统序号 as SensorId,状态 as Status,时间 as Time,数值 as Value,断电否 as IsPowerOff,断电信息 as PowerOffMsg,Convert(nvarchar,备注) as Describe  from 开关数据表" + date.ToString("yyyyMMdd").Substring(2) + " where 系统序号=" + SensorId.ToString() + " and 时间>='" + StartTable + "' and 时间<='" + EndTable + "'";

            }
            cmdstr += " order by 时间";
            List<SwitchData> list = localDB.QueryList<SwitchData>(cmdstr);

            #region  wcccccc
            if (list != null && list.Count >= 2)
            {
                //如果1-5 只有2-4 那么 就扩展为1-5
                if (list.FirstOrDefault().Time > StartTable)
                {
                    list.FirstOrDefault().Time = StartTable;
                }
                if (list.Last().Time < EndTable)
                {
                    list.Last().Time = EndTable;
                }
            }
            if (list == null || list.Count == 0)
            {
                cmdstr = "select 系统序号 as SensorId,状态 as Status,时间 as Time,数值 as Value,断电否 as IsPowerOff,断电信息 as PowerOffMsg,Convert(nvarchar,备注) as Describe  from 开关数据表" + date.ToString("yyyyMMdd").Substring(2) + " where 系统序号=" + SensorId.ToString() + " and 时间<'" + StartTable + "' order by Time desc";
                List<SwitchData> startlist = localDB.QueryList<SwitchData>(cmdstr);
                cmdstr = "select 系统序号 as SensorId,状态 as Status,时间 as Time,数值 as Value,断电否 as IsPowerOff,断电信息 as PowerOffMsg,Convert(nvarchar,备注) as Describe  from 开关数据表" + date.ToString("yyyyMMdd").Substring(2) + " where 系统序号=" + SensorId.ToString() + " and 时间>'" + EndTable + "' order by Time ";
                List<SwitchData> endlist = localDB.QueryList<SwitchData>(cmdstr);
                if (startlist!=null && startlist.FirstOrDefault() != null)
                {
                    startlist.FirstOrDefault().Time = StartTable;
                    list.Add(startlist.FirstOrDefault());
                }
                if (endlist!=null && endlist.FirstOrDefault() != null)
                {
                    endlist.FirstOrDefault().Time = EndTable;
                    list.Add(endlist.FirstOrDefault());
                }


            }
            #endregion

            //if (list == null || list.Count == 0)
            //{
            //    cmdstr = "select 系统序号 as SensorId,状态 as Status,时间 as Time,数值 as Value,断电否 as IsPowerOff,断电信息 as PowerOffMsg,Convert(nvarchar,备注) as Describe  from 开关数据表" + date.ToString("yyyyMMdd").Substring(2) + " where 系统序号=" + SensorId.ToString() + " and 时间>'" + EndTable + "'";
            //    list = db.QueryList<SwitchData>(cmdstr);
            //    SwitchData data = new SwitchData();
            //    data.SensorId = SensorId;
            //    data.Time = StartTable;
            //    data.Status = list[0].Status;
            //    list.Clear();
            //    list.Add(data);
            //    data = data.Clone();
            //    data.Time = EndTable;
            //    list.Add((SwitchData)data);
            //}
            return list;
        }
        //获取历史数据表中的开关量值
        public List<HistoryData> GetSwitchDataLongDuration(DateTime StartTable, DateTime EndTable, int SensorId)
        {
            DateTime date = StartTable;
            List<HistoryData> list=null;
            HistoryData firstData=null;
            List<HistoryData> res=null;
            float Minutes;
            //try
            //{
            //    if ((EndTable - StartTable).TotalMinutes <= 120)
            //        list = GetHistoryDataDuration(StartTable, EndTable, SensorId);
            //    else
            //    {
            //        if ((EndTable - StartTable).TotalDays > 180)
            //            Minutes = 60;
            //        else if ((EndTable - StartTable).TotalDays > 90)
            //            Minutes = 30;
            //        else if ((EndTable - StartTable).TotalDays > 30)
            //            Minutes = 15;
            //        else if ((EndTable - StartTable).TotalDays > 3)
            //            Minutes = 5;
            //        else
            //            Minutes = 1;
            //        string cmdstr = "select top(1) 系统序号 As SensorId, 当前值 as CurValue, t as Time from (SELECT   ROW_NUMBER() OVER(PARTITION BY cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) ORDER BY 时间) RowNum   ,[系统序号]   ,[当前值]   ,cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) as t  FROM 历史数据表" + date.ToString("yyyyMMdd").Substring(2) + "   where 系统序号 = " + SensorId.ToString() + "   ) a  where RowNum = 1 order by time";
            //        firstData = db.GetSingle<HistoryData>(cmdstr);
            //        if (firstData == null)
            //        {
            //            cmdstr = "select top(1) 系统序号 As SensorId, 当前值 as CurValue, t as Time from (SELECT   ROW_NUMBER() OVER(PARTITION BY cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) ORDER BY 时间) RowNum   ,[系统序号]   ,[当前值]   ,cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) as t  FROM 历史数据表" + date.ToString("yyyyMMdd").Substring(2) + "   where 系统序号 = " + SensorId.ToString() + "   ) a  where RowNum = 1 order by time desc";
            //            firstData = db.GetSingle<HistoryData>(cmdstr);
            //        }
            //        cmdstr = "select 系统序号 As SensorId, 当前值 as CurValue, t as Time from (SELECT   ROW_NUMBER() OVER(PARTITION BY cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) ORDER BY 时间 DESC) RowNum   ,[系统序号]   ,[当前值]   ,cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) as t  FROM 历史数据表" + date.ToString("yyyyMMdd").Substring(2) + "   where 系统序号 = " + SensorId.ToString()+ " and 时间>='" + StartTable.ToString("yyyy-MM-dd HH:mm:ss") + "'   ) a  where RowNum = 1";
            //        while (DateTime.Compare(date.Date, EndTable.Date) < 0)
            //        {
            //            date = date.AddDays(1);
            //            cmdstr += "union select 系统序号 As SensorId, 当前值 as CurValue, t as Time from (SELECT   ROW_NUMBER() OVER(PARTITION BY cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) ORDER BY 时间 DESC) RowNum   ,[系统序号]   ,[当前值]   ,cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) as t  FROM 历史数据表" + date.ToString("yyyyMMdd").Substring(2) + "   where 系统序号 = " + SensorId.ToString() + "  ) a  where RowNum = 1  order by time";
            //        }
            //        list = db.QueryList<HistoryData>(cmdstr);
            //        if (list == null || list.Count == 0)
            //        {
            //            list = new List<HistoryData>();
            //            cmdstr = "select top(1) 系统序号 as SensorId,当前值 as CurValue,时间 as Time,上传否 as IsUpload ,上传否2 as IsUpload2 ,上传否3 as IsUpload3 ,上传否4 as IsUpload4  from  历史数据表" + StartTable.ToString("yyyyMMdd").Substring(2) + " where 系统序号=" + SensorId.ToString() + " and 时间<='" + StartTable + "' order by 时间 desc";
            //            HistoryData data = db.GetSingle<HistoryData>(cmdstr);
            //            if (data != null)
            //            {
            //                data.Time = StartTable;
            //                list.Add(data);
            //            }
            //            cmdstr = "select top(1) 系统序号 as SensorId,当前值 as CurValue,时间 as Time,上传否 as IsUpload ,上传否2 as IsUpload2 ,上传否3 as IsUpload3 ,上传否4 as IsUpload4  from  历史数据表" + EndTable.ToString("yyyyMMdd").Substring(2) + " where 系统序号=" + SensorId.ToString() + " and 时间>='" + EndTable + "' order by 时间 ";
            //            data = db.GetSingle<HistoryData>(cmdstr);
            //            if (data != null)
            //            {
            //                data.Time = EndTable;
            //                list.Add(data);
            //            }
            //            else//到结束时间，值一直没有改变
            //            {
            //                data = list[0].Clone();
            //                data.Time = EndTable;
            //                list.Add(data);
            //            }
            //        }
            //        else
            //        {
            //            if (list.Count == 1)
            //            {
            //                cmdstr = "select top(1) 系统序号 as SensorId,当前值 as CurValue,时间 as Time,上传否 as IsUpload ,上传否2 as IsUpload2 ,上传否3 as IsUpload3 ,上传否4 as IsUpload4  from  历史数据表" + StartTable.ToString("yyyyMMdd").Substring(2) + " where 系统序号=" + SensorId.ToString() + " and 时间<='" + StartTable + "' order by 时间 desc";
            //                HistoryData data = db.GetSingle<HistoryData>(cmdstr);
            //                if (data != null)
            //                {
            //                    data.Time = StartTable;
            //                    list.Insert(0, data);
            //                }
            //            }
            //            if (list[list.Count - 1].Time < EndTable)
            //            {
            //                DateTime tmpDate;
            //                if (EndTable < DateTime.Now)
            //                    tmpDate = EndTable;
            //                else
            //                    tmpDate = DateTime.Now;
            //                HistoryData data = list[list.Count - 1].Clone();
            //                data.Time = tmpDate;
            //                list.Add(data);
            //            }
            //        }
            //    }
            //    if (list != null && list.Count > 0)
            //    {
            //        if (list.Count == 1)
            //        {
            //           string cmdstr = "select top(1) 系统序号 as SensorId,当前值 as CurValue,时间 as Time,上传否 as IsUpload ,上传否2 as IsUpload2 ,上传否3 as IsUpload3 ,上传否4 as IsUpload4  from  历史数据表" + StartTable.ToString("yyyyMMdd").Substring(2) + " where 系统序号=" + SensorId.ToString() + " and 时间<='" + StartTable + "' order by 时间 desc";
            //            HistoryData data = db.GetSingle<HistoryData>(cmdstr);
            //            if (data != null)
            //            {
            //                data.Time = StartTable;
            //                list.Insert(0, data);
            //            }
            //        }
            //        if (list[list.Count - 1].Time < EndTable)
            //        {
            //            DateTime tmpDate;
            //            if (EndTable < DateTime.Now)
            //                tmpDate = EndTable;
            //            else
            //                tmpDate = DateTime.Now;
            //            HistoryData data = list[list.Count - 1].Clone();
            //            data.Time = tmpDate;
            //            list.Add(data);
            //        }
            //        if (firstData != null)
            //        {
            //            firstData.Time = StartTable;
            //            list.Insert(0, firstData);
            //        }
            //        list = list.OrderBy(s => s.Time).ToList();
            //        if ((EndTable - StartTable).TotalMinutes < 120)//小于2小时按照每秒
            //            list = ExtendAnalogList(list, 0, 1);
            //        else if ((EndTable - StartTable).TotalDays < 3)//小于3天按照1分钟
            //            list = ExtendAnalogList(list, 1, 1);
            //        else //大于3天按照5分钟
            //            list = ExtendAnalogList(list, 1, 5);
            //    }
            //}
            //catch(Exception ex)
            //{
            //    list = null;
            //}
            return list;

         }
        public List<HistoryData> GetSwtichColumn(DateTime StartTable, DateTime EndTable, int SensorId)
        {
            DateTime date = StartTable;
            List<HistoryData> list=null;
            HistoryData firstData;
            List<HistoryData> res = null;
            float Minutes;
            //try
            //{
            //    if ((EndTable - StartTable).TotalMinutes <= 120)
            //        list = GetHistoryDataDuration(StartTable, EndTable, SensorId);
            //    else
            //    {
            //        if ((EndTable - StartTable).TotalDays > 180)
            //            Minutes = 60;
            //        else if ((EndTable - StartTable).TotalDays > 90)
            //            Minutes = 30;
            //        else if ((EndTable - StartTable).TotalDays > 30)
            //            Minutes = 15;
            //        else if ((EndTable - StartTable).TotalDays > 3)
            //            Minutes = 5;
            //        else
            //            Minutes = 1;
            //        string cmdstr = "select top(1) 系统序号 As SensorId, 当前值 as CurValue, t as Time from (SELECT   ROW_NUMBER() OVER(PARTITION BY cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) ORDER BY 时间) RowNum   ,[系统序号]   ,[当前值]   ,cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) as t  FROM 历史数据表" + date.ToString("yyyyMMdd").Substring(2) + "   where 系统序号 = " + SensorId.ToString() + "   ) a  where RowNum = 1 order by time";
            //        firstData = db.GetSingle<HistoryData>(cmdstr);
            //        cmdstr = "select 系统序号 As SensorId, 当前值 as CurValue, t as Time from (SELECT   ROW_NUMBER() OVER(PARTITION BY cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) ORDER BY 时间 DESC) RowNum   ,[系统序号]   ,[当前值]   ,cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) as t  FROM 历史数据表" + date.ToString("yyyyMMdd").Substring(2) + "   where 系统序号 = " + SensorId.ToString() + "   ) a  where RowNum = 1";
            //        //while (DateTime.Compare(date.Date, EndTable.Date) < 0)
            //        //{
            //        //    date = date.AddDays(1);
            //        //    cmdstr += "union select 系统序号 As SensorId, 当前值 as CurValue, t as Time from (SELECT   ROW_NUMBER() OVER(PARTITION BY cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) ORDER BY 时间 DESC) RowNum   ,[系统序号]   ,[当前值]   ,cast(floor(cast(时间 as float) * 24 * 60 / " + Minutes + ") * " + Minutes + " / 60 / 24 as smalldatetime) as t  FROM 历史数据表" + date.ToString("yyyyMMdd").Substring(2) + "   where 系统序号 = " + SensorId.ToString() + "  ) a  where RowNum = 1  order by time";
            //        //}
            //        list = db.QueryList<HistoryData>(cmdstr);
            //        if (list == null || list.Count == 0)
            //        {
            //            list = new List<HistoryData>();
            //            cmdstr = "select top(1) 系统序号 as SensorId,当前值 as CurValue,时间 as Time,上传否 as IsUpload ,上传否2 as IsUpload2 ,上传否3 as IsUpload3 ,上传否4 as IsUpload4  from  历史数据表" + StartTable.ToString("yyyyMMdd").Substring(2) + " where 系统序号=" + SensorId.ToString() + " and 时间<='" + StartTable + "' order by 时间 desc";
            //            HistoryData data = db.GetSingle<HistoryData>(cmdstr);
            //            if (data != null)
            //            {
            //                data.Time = StartTable;
            //                list.Add(data);
            //            }
            //            cmdstr = "select top(1) 系统序号 as SensorId,当前值 as CurValue,时间 as Time,上传否 as IsUpload ,上传否2 as IsUpload2 ,上传否3 as IsUpload3 ,上传否4 as IsUpload4  from  历史数据表" + EndTable.ToString("yyyyMMdd").Substring(2) + " where 系统序号=" + SensorId.ToString() + " and 时间>='" + EndTable + "' order by 时间 ";
            //            data = db.GetSingle<HistoryData>(cmdstr);
            //            if (data != null)
            //            {
            //                data.Time = EndTable;
            //                list.Add(data);
            //            }
            //            else//到结束时间，值一直没有改变
            //            {
            //                data = list[0].Clone();
            //                data.Time = EndTable;
            //                list.Add(data);
            //            }
            //        }
            //        else
            //        {
            //            if (list.Count == 1)
            //            {
            //                cmdstr = "select top(1) 系统序号 as SensorId,当前值 as CurValue,时间 as Time,上传否 as IsUpload ,上传否2 as IsUpload2 ,上传否3 as IsUpload3 ,上传否4 as IsUpload4  from  历史数据表" + StartTable.ToString("yyyyMMdd").Substring(2) + " where 系统序号=" + SensorId.ToString() + " and 时间<='" + StartTable + "' order by 时间 desc";
            //                HistoryData data = db.GetSingle<HistoryData>(cmdstr);
            //                if (data != null)
            //                {
            //                    data.Time = StartTable;
            //                    list.Insert(0, data);
            //                }
            //            }
            //            if (list[list.Count - 1].Time < EndTable)
            //            {
            //                DateTime tmpDate;
            //                if (EndTable < DateTime.Now)
            //                    tmpDate = EndTable;
            //                else
            //                    tmpDate = DateTime.Now;
            //                HistoryData data = list[list.Count - 1].Clone();
            //                data.Time = tmpDate;
            //                list.Add(data);
            //            }
            //        }
            //    }
            //    if (list != null && list.Count > 0)
            //    {
            //        if ((EndTable - StartTable).TotalMinutes < 120)//小于2小时按照每秒
            //            list = ExtendAnalogList(list, 0, 1);
            //        else if ((EndTable - StartTable).TotalDays < 3)//小于3天按照1分钟
            //            list = ExtendAnalogList(list, 1, 1);
            //        else //大于3天按照5分钟
            //            list = ExtendAnalogList(list, 1, 5);
            //    }
            //}
            //catch (Exception ex)
            //{
            //    list = null;
            //}
            return list;
        }
        //}
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sourcelist"></param>
        /// <param name="grain">粒度，0为秒，1为分钟</param>
        ///  <param name="TimeUnit">时间间隔数值</param>
        /// <returns></returns>
        public List<List<SwitchData>> ExtendSwitchList(List<SwitchData> sourcelist, int grain = 0, int TimeUnit = 5)
        {
            if (sourcelist == null)
                return null;
            double duration;
            int count = sourcelist.Count;
            SwitchData switchData=new SwitchData();

            if (grain == 1)
            {
                DateTime date = sourcelist[count - 1].Time;
                DateTime nextDay = DateTime.ParseExact(date.ToString("yyyyMMdd"), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture).AddDays(1);
                switchData.Time = nextDay;
                switchData.Status = sourcelist[count - 1].Status;
                sourcelist.Add(switchData);
                //
                date = DateTime.ParseExact(sourcelist[0].Time.ToString("yyyyMMdd"), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture).AddMilliseconds(-1);
                switchData = new SwitchData();
                switchData.Status = sourcelist[0].Status;
                switchData.Time = date;
                sourcelist.Insert(0, switchData);
            }
            count = sourcelist.Count;
            List<List<SwitchData>> ret = new List<List<SwitchData>>();
            for (int i = 0; i < count-1 ; i++)
            {
                List<SwitchData> list = new List<SwitchData>();
                list.Add(sourcelist[i]);
                if (sourcelist[i].Time != sourcelist[i + 1].Time)
                {
                    TimeSpan ts = sourcelist[i + 1].Time - sourcelist[i].Time;
                    if (grain == 0)
                        duration = ts.TotalSeconds;
                    else
                        duration = ts.TotalMinutes;
                    for (int j = 0; j < duration - 1; j++)
                    {
                        SwitchData data = sourcelist[i].Clone();
                        if (grain == 0)
                            data.Time = data.Time.AddSeconds(j * TimeUnit + 1);
                        else
                            data.Time = data.Time.AddMinutes(j * TimeUnit + 1);
                        list.Add(data);
                    }
                    list.Add(sourcelist[i + 1].Clone());
                }
                ret.Add(list);
            }
            return ret;
        }
        public List<List<SwitchData>> ExtendCoarseSwitchList(List<SwitchData> sourcelist)
        {
            int count = sourcelist.Count;
            List<List<SwitchData>> ret = new List<List<SwitchData>>();
            for (int i = 0; i < count - 1; i++)
            {
                List<SwitchData> list = new List<SwitchData>();
                list.Add(sourcelist[i]);
                SwitchData data = sourcelist[i + 1].Clone();
                data.Status = sourcelist[i].Status;
                list.Add(data);
                ret.Add(list);
            }
            return ret;
        }

        #endregion


        /// <summary>
        /// 逻辑报警配置表  (查、插入)
        /// </summary>
        /// <returns></returns>
        #region 逻辑报警配置表
        public List<LogicAlarm> GetLogicAlarmList(int valid=0)
        {
            string cmdstr = "select ID as Id,有效否 as IsValid,项目名称 as ProjectName,";
            cmdstr += "条件1有效否 as Conditional1Isvalid,传感器1 as Sensor1,条件1 as Conditional1,数值1 as Value1,时长1 as Duration1,";
            cmdstr += "条件2有效否 as Conditional2Isvalid,传感器2 as Sensor2,条件2 as Conditional2,数值2 as Value2,时长2 as Duration2,";
            cmdstr += "条件3有效否 as Conditional3Isvalid,传感器3 as Sensor3,条件3 as Conditional3,数值3 as Value3,时长3 as Duration3,";
            cmdstr += "条件4有效否 as Conditional4Isvalid,传感器4 as Sensor4,条件4 as Conditional4,数值4 as Value4,时长4 as Duration4,";
            cmdstr += "条件5有效否 as Conditional5Isvalid,传感器5 as Sensor5,条件5 as Conditional5,数值5 as Value5,时长5 as Duration5,";
            cmdstr += "条件1或 as Condition1,条件2或 as Condition2,条件3或 as Condition3,条件4或 as Condition4,条件5或 as Condition5,";
            cmdstr += "开出关联 as OpenRelate,断电区域 as OffpowerArea,是否应急联动 as IsEmergencyResponse,人员定位 as PersonLocation,语音广播 as VoiceCast,类型 as Type from 逻辑报警配置表 ";
            if (valid == 1)
                cmdstr += " where 有效否=1 ";
            cmdstr+=" order by ID";
            List<LogicAlarm> list = remoteDB.QueryCacheList<LogicAlarm>(LogicAlarmListKey,cmdstr);
            return list;
        }


        public void UpdateLogicAlarm(LogicAlarm logicAlarm,DateTime time,int status,string msg)
        {
            string cmd = "update 逻辑报警配置表";
            cmd += " set 是否应急联动=" + status;
            cmd += ", 时间='"+time +"', 状态='"+msg+"' ";
            cmd += " where ID=" + logicAlarm.Id;
            remoteDB.SqlExecute(cmd);
            LocalCache.Remove(LogicAlarmListKey);
        }


        public LogicAlarm SelectLogicAlarmById(string logicAlarmId)
        {
            string cmdstr = "select ID as Id,有效否 as IsValid,项目名称 as ProjectName,";
            cmdstr += "条件1有效否 as Conditional1Isvalid,传感器1 as Sensor1,条件1 as Conditional1,数值1 as Value1,时长1 as Duration1,";
            cmdstr += "条件2有效否 as Conditional2Isvalid,传感器2 as Sensor2,条件2 as Conditional2,数值2 as Value2,时长2 as Duration2,";
            cmdstr += "条件3有效否 as Conditional3Isvalid,传感器3 as Sensor3,条件3 as Conditional3,数值3 as Value3,时长3 as Duration3,";
            cmdstr += "条件4有效否 as Conditional4Isvalid,传感器4 as Sensor4,条件4 as Conditional4,数值4 as Value4,时长4 as Duration4,";
            cmdstr += "条件5有效否 as Conditional5Isvalid,传感器5 as Sensor5,条件5 as Conditional5,数值5 as Value5,时长5 as Duration5,";
            cmdstr += "条件1或 as Condition1,条件2或 as Condition2,条件3或 as Condition3,条件4或 as Condition4,条件5或 as Condition5,";
            cmdstr += "开出关联 as OpenRelate,断电区域 as OffpowerArea,是否应急联动 as IsEmergencyResponse,人员定位 as PersonLocation,语音广播 as VoiceCast,类型 as Type from 逻辑报警配置表 ";
            cmdstr += " where ID=" + logicAlarmId;
            LogicAlarm logicAlarm = remoteDB.GetSingle<LogicAlarm>(cmdstr);
            return logicAlarm;
        }
        public int AddLogicAlarm(LogicAlarm logicAlarm)
        {
            LogicAlarm logicala =SelectLogicAlarmById(logicAlarm.Id.ToString());
            if(logicala != null)
            {
                return 0;
            }
            else
            {
                string cmd = "insert into 逻辑报警配置表 ([ID]\r\n           ,[有效否]\r\n           ,[项目名称]\r\n           ,[条件1有效否]\r\n           ,[传感器1]\r\n           ,[条件1]\r\n           ,[数值1]\r\n           ,[时长1]\r\n           ,[条件2有效否]\r\n           ,[传感器2]\r\n           ,[条件2]\r\n           ,[数值2]\r\n           ,[时长2]\r\n           ,[条件3有效否]\r\n           ,[传感器3]\r\n           ,[条件3]\r\n           ,[数值3]\r\n           ,[时长3]\r\n           ,[条件4有效否]\r\n           ,[传感器4]\r\n           ,[条件4]\r\n           ,[数值4]\r\n           ,[时长4]\r\n           ,[条件5有效否]\r\n           ,[传感器5]\r\n           ,[条件5]\r\n           ,[数值5]\r\n           ,[时长5]\r\n           ,[开出关联]\r\n           ,[断电区域]\r\n           ,[是否应急联动]\r\n           ,[人员定位]\r\n           ,[语音广播]\r\n           ,[状态]\r\n           ,[时间]\r\n           ,[电话号码]\r\n           ,[条件1或]\r\n           ,[条件2或]\r\n           ,[条件3或]\r\n           ,[条件4或]\r\n           ,[条件5或]\r\n           ,[类型])";
                cmd += " values ('" + logicAlarm.Id + "','" + logicAlarm.IsValid + "','" + logicAlarm.ProjectName + "','";
                cmd += logicAlarm.Conditional1Isvalid + "','" + logicAlarm.Sensor1 + "','" + logicAlarm.Conditional1 + "','" + logicAlarm.Value1 + "','" + logicAlarm.Duration1 + "','";
                cmd += logicAlarm.Conditional2Isvalid + "','" + logicAlarm.Sensor2 + "','" + logicAlarm.Conditional2 + "','" + logicAlarm.Value2 + "','" + logicAlarm.Duration2 + "','";
                cmd += logicAlarm.Conditional3Isvalid + "','" + logicAlarm.Sensor3 + "','" + logicAlarm.Conditional3 + "','" + logicAlarm.Value3 + "','" + logicAlarm.Duration3 + "','";
                cmd += logicAlarm.Conditional4Isvalid + "','" + logicAlarm.Sensor4 + "','" + logicAlarm.Conditional4 + "','" + logicAlarm.Value4 + "','" + logicAlarm.Duration4 + "','";
                cmd += logicAlarm.Conditional5Isvalid + "','" + logicAlarm.Sensor5 + "','" + logicAlarm.Conditional5 + "','" + logicAlarm.Value5 + "','" + logicAlarm.Duration5 + "','";
                cmd += logicAlarm.OpenRelate + "','" + logicAlarm.OffpowerArea + "','"+ logicAlarm.IsEmergencyResponse + "','" + logicAlarm.PersonLocation + "','" + logicAlarm.VoiceCast + "','";
                cmd += logicAlarm.Phone + "','" + logicAlarm.Status + "'," + (logicAlarm.Time == null ? @"null" : logicAlarm.Time) + ",'"  + logicAlarm.Condition1 + "','" ;
                cmd += logicAlarm.Condition2 + "','" + logicAlarm.Condition3 + "','" + logicAlarm.Condition4 + "','" + logicAlarm.Condition5;
                cmd += "',"+logicAlarm.Type+") ";
                int i = localDB.SqlExecute(cmd);
                LocalCache.Remove(LogicAlarmListKey);
                return i;
            }
        }
        public int DeleteLogicAlarmById(string Id)
        {
         string cmd = "delete from  逻辑报警配置表 where ID =" + Id;
         int i = localDB.SqlExecute(cmd);
         LocalCache.Remove(LogicAlarmListKey);
         return i;

        }

        public int UpdateLogicAlarmById(LogicAlarm logicAlarm)
        {
            string cmd = "UPDATE [dbo].[逻辑报警配置表]   SET 有效否 = " +  logicAlarm.IsValid  + " ,项目名称 = '" + logicAlarm.ProjectName + "' ,条件1有效否 = " + logicAlarm.Conditional1Isvalid + " ,传感器1 = '"+ logicAlarm.Sensor1 + "',条件1 ='" + logicAlarm.Conditional1  + "' ,数值1 =  '" + logicAlarm.Value1 + "' ,时长1 = " +   logicAlarm.Duration1 +"  ,条件2有效否 =  " +logicAlarm.Conditional2Isvalid +"  ,传感器2 = '" + logicAlarm.Sensor2 + "' ,条件2  = '"+ logicAlarm.Conditional2 +"' ,数值2 = '"+ logicAlarm.Value2  + "' ,时长2 = " + logicAlarm.Duration2 + ",条件3有效否 = " + logicAlarm.Conditional3Isvalid +"  ,传感器3 = '" + logicAlarm.Sensor3   +"' ,条件3 = '" + logicAlarm.Conditional3 + "' ,数值3 ='" + logicAlarm.Value3 + " ',时长3 = " + logicAlarm.Duration3  + ",条件4有效否 =" + logicAlarm.Conditional4Isvalid +"  ,传感器4 = '" +  logicAlarm.Sensor4 + "' ,条件4 = '" + logicAlarm.Conditional4 + "',数值4 = '" + logicAlarm.Value4 + "'  ,时长4 =" +logicAlarm.Duration4 +",条件5有效否 = "+logicAlarm.Conditional5Isvalid  + " ,传感器5 = '" +logicAlarm.Sensor5+"' ,条件5 = '" + logicAlarm.Conditional5 + "' ,数值5 = '"+ logicAlarm.Value5 + "'  ,时长5 =" + logicAlarm.Duration5 + " ,开出关联 = '" + logicAlarm.OpenRelate + "' ,断电区域 = '" + logicAlarm.OffpowerArea + "',人员定位 = '" + logicAlarm.PersonLocation + "',语音广播 = ' " + logicAlarm.VoiceCast  + "',电话号码 = '" +  logicAlarm.Phone + "'  ,时间 ='" +   logicAlarm.Time  + "',类型 =  " + logicAlarm. Type + " ,条件1或 = " + logicAlarm.Condition1  + " ,条件2或 = "+ logicAlarm.Condition2  +" ,条件3或 = " + logicAlarm.Condition3   + ",条件4或 = " + logicAlarm.Condition4 + " ,条件5或 = " + logicAlarm.Condition5 +  " WHERE ID =" + logicAlarm.Id;
            int i = localDB.SqlExecute(cmd);
            return i;
        }

        public string SelectMaxId()
        {
            string cmd = "SELECT MAX(ID) FROM 逻辑报警配置表";
            string s = localDB.GetSingle<string>(cmd);
            if (s == null)
                s = "0";
            return s;
        }
        public void SyncLogicAlarm()
        {
            string str = "delete  from 逻辑报警配置表";
            localDB.SqlExecute(str);
            List<LogicAlarm> data = GetLogicAlarmList();
            foreach (LogicAlarm item in data)
            {
                AddLogicAlarm(item);
            }

        }
        #endregion


        /// <summary>
        /// 用户操作日志表  (插入)
        /// </summary>
        /// <returns></returns> 
        #region 用户操作日志表
        public int AddUserAction(string user , DateTime time ,string describe )
        {
                string cmd = "insert into 用户日志(用户,时间,操作备注) values('" + user + "','" +  (time == null ? "null" : time) + "','" + describe + "')";
                int i = localDB.SqlExecute(cmd);
                return i;
        }
        public List<UserLog> GetUserLogs(DateTime start,DateTime end,string user="")
        {
            string cmd = "SELECT 用户 as UserName,时间 as OperateTime,操作备注 as Message from  用户日志 where 1=1";
            if (start != new DateTime(0))
                cmd += " and 时间>='"+start.ToString("yyyy-MM-dd HH:mm:ss")+"'";
            if (end != new DateTime(0))
                cmd += " and 时间<='" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            if(!string.IsNullOrEmpty(user.Trim()))
                    cmd += " and 用户='"+user+"'";
            cmd += " order by 时间 desc";
            List<UserLog> list=localDB.QueryList<UserLog>(cmd);
            return list;
        }
        #endregion


        /// <summary>
        /// 获取关联传感器
        /// </summary>
        /// <returns></returns>
        #region 关联传感器
        //分站地址
        public List<string> GetRelateAddr()
        {
            string cmdstr = "select distinct 分站地址 from 站点配置表";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        public DataTable getBrach()
        {
            string cmdstr = "select distinct 端口序号 as portid,CONVERT(VARCHAR(50),端口序号)+'-'+传感器名称 as sensorname from 端口参数表 where 分站地址=67 and 是否已删除=0";
            DataTable list = localDB.DataWorke(cmdstr);
            return list;
        }
        //断电控制端口
        public List<string> GetPowOffPort(string BrachId)
        {
            string cmdstr = "select distinct 断电控制端口 from 端口参数表 where 分站地址 = "+BrachId;
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        //馈电关联
        public string GetAbnormalfeed(string BrachId)
        {
            string cmdstr = "select 馈电关联监测 from 站点配置表 where 分站地址 = " + BrachId;
            string list = localDB.GetSingle<string>(cmdstr);
            if (list.Trim('\b').Length == 0)
            {
                cmdstr = "update 站点配置表 set 馈电关联监测=';;;;;;;' where 分站地址 = " + BrachId;
                localDB.SqlExecute(cmdstr);
            }
            else if(list.IndexOf(';')>=0)
            {
                cmdstr = "update 站点配置表 set 馈电关联监测='"+list.Trim('\b')+"' where 分站地址 = " + BrachId;
                localDB.SqlExecute(cmdstr);
            }
            else 
            {
                cmdstr = "update 站点配置表 set 馈电关联监测='" + list.Replace('\b',';') + "' where 分站地址 = " + BrachId;
                localDB.SqlExecute(cmdstr);
            }
            return list;
        }

        public string GetOpenPortName(string BrachId)
        {
            string cmdstr = "select 开出端口名称 from 站点配置表 where 分站地址 = " + BrachId;
            string list = localDB.GetSingle<string>(cmdstr);
            if (string.IsNullOrEmpty(list))
            {
                cmdstr = "update 站点配置表 set 开出端口名称=';;;;;;;' where 分站地址 = " + BrachId;
                localDB.SqlExecute(cmdstr);
            }
            else if (list.Trim('\b').Length == 0)
            {
                cmdstr = "update 站点配置表 set 开出端口名称=';;;;;;;' where 分站地址 = " + BrachId;
                localDB.SqlExecute(cmdstr);
            }
            else if (list.IndexOf(';') >= 0)
            {
                cmdstr = "update 站点配置表 set 开出端口名称='" + list.Trim('\b') + "' where 分站地址 = " + BrachId;
                localDB.SqlExecute(cmdstr);
            }
            else
            {
                cmdstr = "update 站点配置表 set 开出端口名称='" + list.Replace('\b', ';') + "' where 分站地址 = " + BrachId;
                localDB.SqlExecute(cmdstr);
            }
            GetAbnormalfeed(BrachId);
            return list;
        }


        public string GetMinPortId()
        {
            string cmdstr = "";
            return "";
        }
        //获取所有传感器端口号
        public List<string> GetAllPortId(string StationId, int IsAnalog)
        {
            string cmdstr = "SELECT  端口序号  \r\n  FROM [dbo].[端口参数表]\r\n  where 分站地址=" + StationId + " and  是否已删除=0 and 是否有效=1 and 是否模拟量=" + IsAnalog + "";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }

        //获取所有传感器名称
        public List<string> GetAllSensorName()
        {
            string cmdstr = "select distinct 传感器名称 from 端口参数表 ";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        //获取所有安装地点
        public List<string> GetAllInstallLocation()
        {
            string cmdstr = "select distinct 安装地点 from 端口参数表 ";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        //获取所有地址类型
        public List<string> GetAllLocationType()
        {
            string cmdstr = "select distinct 地址类型 from 端口参数表 ";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        //工作面类型
        public List<string> GetAllWorkType()
        {
            string cmdstr = "select distinct 工作面类型 from 端口参数表 ";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        //甲烷传感器编号
        public List<string> GetAllGasSensorId()
        {

            return new List<string> {"T0","T1","T2","T3","T4","T5","T6","T7","T8","T9" };
        }


        //传感器1
        public List<string> GetAllSensor1()
        {
            string cmdstr = "select distinct 传感器1 from 逻辑报警配置表";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        //传感器2
        public List<string> GetAllSensor2()
        {
            string cmdstr = "select distinct 传感器2 from 逻辑报警配置表";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        //传感器3
        public List<string> GetAllSensor3()
        {
            string cmdstr = "select distinct 传感器3 from 逻辑报警配置表";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        //传感器4
        public List<string> GetAllSensor4()
        {
            string cmdstr = "select distinct 传感器4 from 逻辑报警配置表";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        //传感器5
        public List<string> GetAllSensor5()
        {
            string cmdstr = "select distinct 传感器5 from 逻辑报警配置表";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }

        //条件1
        public List<string> GetAllCondition1()
        {
            string cmdstr = "select distinct 条件1 from 逻辑报警配置表";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        //条件2
        public List<string> GetAllCondition2()
        {
            string cmdstr = "select distinct 条件2 from 逻辑报警配置表";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        //条件3
        public List<string> GetAllCondition3()
        {
            string cmdstr = "select distinct 条件3 from 逻辑报警配置表";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        //条件4
        public List<string> GetAllCondition4()
        {
            string cmdstr = "select distinct 条件4 from 逻辑报警配置表";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }
        //条件5
        public List<string> GetAllCondition5()
        {
            string cmdstr = "select distinct 条件5 from 逻辑报警配置表";
            List<string> list = localDB.QueryList<string>(cmdstr);
            return list;
        }

        #endregion


        /// <summary>
        /// 获取某些字段
        /// </summary>
        /// <returns></returns>
        #region 杂项
        //获取分站地址
        public string GetBrachBySensorId(string SensorId)
        {
            string cmd = "select 分站地址 from 端口参数表 where 系统序号 = " + SensorId;
            string brachId = localDB.GetSingle<string>(cmd);
            return brachId;
        }
        //获取字符串
        public string GetSensorStringBySensorId(string SensorId)
        {
            string cmd = "select CONVERT(VARCHAR(50),分站地址)+'-'+ CONVERT(VARCHAR(50),端口序号)+'/'+CONVERT(VARCHAR(50), 系统序号)+'/'+ 传感器名称+'/'+ 安装地点 from 端口参数表 where 系统序号 = " + SensorId;
            string SensorString = localDB.GetSingle<string>(cmd);
            return SensorString;
        }

        //获取分站Ip
        public string GetIPByBrachId(string BrachId)
        {
            string cmd = "select 分站IP from 站点配置表 where 分站地址 = " + BrachId;
            string IP = localDB.GetSingle<string>(cmd);
            return IP;
        }
        //获取系统序号最大值
        public string GetMaxSenorId()
        {
            string cmd = "select max(系统序号) from  端口参数表 ";
            string senorId = localDB.GetSingle<string>(cmd);
            if (string.IsNullOrEmpty(senorId))
                return "0";
            else
                return senorId;
        }

        //获取分站地址最大值
        public string GetMaxBrachId()
        {
            string cmd = "select max(分站地址) from  站点配置表 ";
            string BrachId = localDB.GetSingle<string>(cmd);
            if (string.IsNullOrEmpty(BrachId))
                return "0";
            else
                return BrachId;
        }
        

        //获取所有分站
        public List<string> GetSiteConfig()
        {
            string cmdstr = "select cast(分站地址 as varchar)   + '-' + 分站名称 + '-' + 安装地点 from 站点配置表 where 有效否=1 order by 分站地址";

            List<string> data = localDB.QueryList<string>(cmdstr);
            return data;
        }

        //获取所有断电控制端口 
        public List<string> GetAllOffPowerPort(string brachId)
        {
            string cmdstr = "select  distinct 断电控制端口 from 端口参数表 where 分站地址 = " + brachId +" and 是否已删除=0 and 是否有效 =1 and 是否断电控制 =1 ";

            List<string> data = localDB.QueryList<string>(cmdstr);
            return data;
        }

        //下发主站请求帧时更新分级上限及分级限时
        public void UpdatePortParaLevel(PortPara portPara)
        {
            string cmd = "update 端口参数表 set 是否有效=" + portPara.IsValid.ToString();
            cmd += ",分级上限1=" + (portPara.HighClassify1 == null ? @"null" : portPara.HighClassify1);
            cmd += ",分级上限2=" + (portPara.HighClassify2 == null ? @"null" : portPara.HighClassify2);
            cmd += ",分级上限3=" + (portPara.HighClassify3 == null ? @"null" : portPara.HighClassify3);
            cmd += ",分级上限4=" + (portPara.HighClassify4 == null ? @"null" : portPara.HighClassify4);
            cmd += ",分级限时1=" + (portPara.ClassifyTime1 == null ? @"null" : portPara.ClassifyTime1);
            cmd += ",分级限时2=" + (portPara.ClassifyTime2 == null ? @"null" : portPara.ClassifyTime2);
            cmd += ",分级限时3=" + (portPara.ClassifyTime3 == null ? @"null" : portPara.ClassifyTime3);
            cmd += ",分级限时4=" + (portPara.ClassifyTime4 == null ? @"null" : portPara.ClassifyTime4);
            cmd += ",是否分级报警=" + portPara.IsGradingAlarm;
            cmd += " where 系统序号=" + portPara.SensorId.ToString();
            localDB.SqlExecute(cmd);
            LocalCache.Remove(PortConfigKey);

        }
        //获取传感器ID
        public string GetSensorId(string brachId,string portId)
        {
            string cmd = "select 系统序号 from 端口参数表 where 分站地址 = " + brachId;
            cmd += " and 端口序号 =" + portId;
            cmd += " and 是否有效 =" + 1;
            cmd += " and 是否已删除 =" + 0;
            string SensorId = localDB.GetSingle<string>(cmd);
            return SensorId;
        }

        //获取单位 
        public string GetSensorUnit(string sensortype)
        {
            string cmd = " select distinct 单位  from 端口参数表 where 传感器类型 = " + sensortype;

            List<string> Sensor = localDB.QueryList<string>(cmd);
            if (Sensor.Count > 0)
            {
                return Sensor.FirstOrDefault();
            }
            else
            {
                return "";
            }
        }



         public void UpdatePowerOffPort(string t)
        {
            if(t!= null && t != "")
            {
               string[] str =  t.Split('-');
               string brach = str[0];
               string port = str[1];
               string cmd = "select 系统序号 from 端口参数表 where 分站地址 = " + brach + "  and 端口序号 = " + port + " and  是否有效 = 1 and 是否已删除 = 0 ";
               string SensorId = localDB.GetSingle<string>(cmd);
               string cmd2 = "update 端口参数表 set 断电控制端口 = " + t + " where 系统序号 = " + SensorId;
               int i = localDB.SqlExecute(cmd2);
            }
        }
        public List<string> GetPortByBrachAndPort(string brachId,int portId)
        {
            string cmdstr = "select distinct 断电控制端口 from 端口参数表 where 分站地址 = "+ brachId + " and 端口序号 = " + portId;
            List<string> data = localDB.QueryList<string>(cmdstr);
            return data;
        }



        //获取断电控制关联的传感器 
        public List<string> GetOffPowerRelatePort(string port)
        {
            string cmdstr = "select cast(分站地址 as varchar) + '-' + cast(端口序号 as varchar) from 端口参数表 where (断电控制端口 like '%,"+ port+ "' or 断电控制端口 like '"+ port+ ",%' or 断电控制端口 like '%,"+ port+ ",%' or 断电控制端口 = '"+ port+ "') and 是否有效 = 1 and 是否已删除 = 0 and 是否断电控制 =1";
            List<string> data = localDB.QueryList<string>(cmdstr);
            return data;
        }

        //获取分站是否就地断电
        public string GetIsPowOffCur(string brachId)
        {
            string cmdstr = " select 是否就地断电 from 站点配置表 where 分站地址 = " + brachId;
            string data = localDB.GetSingle<string>(cmdstr);
            return data;
        }

        //获取已存在的最小端口号
        public int GetMinPortInStation(string station)
        {
            string cmdstr = "  select  端口序号   from   [端口参数表]  where 分站地址 = "+ station +  " and 是否有效 = 1 and 是否已删除 =0  order   by  端口序号 ";
            List<string> data = localDB.QueryList<string>(cmdstr);
            for (int i = 1; i < 13; i++)
            {
                if (!data.Contains(i.ToString()))
                {
                    return i;
                }
            }
            return 1;
        }

        public List<string> GetPort(string port)
        {
            string cmd = "SELECT   CONVERT(VARCHAR(50),[分站地址])+'-'+CONVERT(VARCHAR(50),[端口序号]) FROM [dbo].[端口参数表]\r\n  \r\nwhere  断电控制端口 like '%" + port + "%' and 是否有效 =1 and 是否已删除 =0 and 是否断电控制 = 1 \r\n\r\n";
            List<string> data = localDB.QueryList<string>(cmd);
            return data;
        }
        public List<string> GetPortByStation(string stationId)
        {
            string cmd = "SELECT    CONVERT(VARCHAR(50),[分站地址])+'-'+CONVERT(VARCHAR(50),[端口序号]) FROM [dbo].[端口参数表]\r\n  \r\nwhere  断电控制端口 like '%" + stationId + "-%' and 是否有效 =1 and 是否已删除 =0 and 是否断电控制 = 1 \r\n\r\n";
            List<string> data = localDB.QueryList<string>(cmd);
            return data;
        }
        #endregion

        ///风电瓦斯参数表
        #region 风电瓦斯参数表
        //public List<InterMode> GetInterModeList()
        //{
        //    string cmdstr = "select 分站编号 as StationId,风电瓦斯闭锁模式 as InMode,数值1 as Value1,数值2 as Value2,数值3 as Value3,数值4 as Value4,数值5 as Value5,数值6 as Value6 from 风电瓦斯配置参数表 ";
        //    cmdstr += " order by 分站编号";
        //    List<InterMode> list = db.QueryList<InterMode>(cmdstr);
        //    return list;
        //}

        //+ "','" + immediate.MaxValue
        public InterMode GetInterModeById(string BrachAdressId)
        {
            InterMode data = null;
            string cmdstr = "select 分站编号 as StationId,风电瓦斯闭锁模式 as InMode,数值1 as Value1,数值2 as Value2,数值3 as Value3,数值4 as Value4,数值5 as Value5,数值6 as Value6 from 风电瓦斯配置参数表 where 分站编号=" + BrachAdressId;
            data =  localDB.DataWorker<InterMode>(cmdstr);
            return data;
        }

        public int AddInterMode(InterMode interMode)
        {
            string cmdstr = "Insert into 风电瓦斯配置参数表 ([分站编号] ,[风电瓦斯闭锁模式],[数值1],[数值2],[数值3],[数值4],[数值5],[数值6])  VALUES  (" + interMode.StationId + ',' + interMode.InMode + ',' + interMode.Value1 + ',' + interMode.Value2 + ',' + interMode.Value3 + ',' + interMode.Value4 + ',' + interMode.Value5 + ',' + interMode.Value6 + ")";
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }
        public void UpdateInterMode(InterMode interMode)
        {
            string cmd = " update 风电瓦斯配置参数表 ";
            cmd += " set 分站编号 = " + interMode.StationId;
            cmd += ",风电瓦斯闭锁模式 = " + interMode.InMode;
            cmd += ",数值1 = " + interMode.Value1;
            cmd += ",数值2 = " + interMode.Value2;
            cmd += ",数值3 = " + interMode.Value3;
            cmd += ",数值4 = " + interMode.Value4;
            cmd += ",数值5 = " + interMode.Value5;
            cmd += ",数值6 = " + interMode.Value6;
            cmd += "where 分站编号 = " + interMode.StationId;
            localDB.SqlExecute(cmd);
        }

        //public void CreateBrachStatusTable(string yymmdd)
        //{
        //    string cmdstr = "IF OBJECT_ID('分站状态记录表" + yymmdd + "','u') IS NULL BEGIN CREATE TABLE [dbo].[分站状态记录表" + yymmdd + "]( [分站地址] [smallint] NOT NULL, [时间] [datetime] NULL, [分站状态] [smallint] NULL, [数值] [float] NULL, [上传否] [bit] NULL, [上传否2] [bit] NULL, [上传否3] [bit] NULL, [上传否4] [bit] NULL) ON [PRIMARY] SELECT 1 END ELSE SELECT 0";
        //    db.SqlExecute(cmdstr);
        //}


        #endregion


        ///历史逻辑数据表
        #region 历史逻辑数据表
        public List<LogicHistory> GetLogicHistoryList()
        {
            string cmdstr = "SELECT [id], [传感器名称]\r\n as  Sensor ,[时间]\r\n  as Time,[报警信息]\r\n as AlarmMsg ,[报警状态]\r\n as Status ,[备注]\r\n as Describe  FROM [dbo].[历史逻辑报警表]";
            List<LogicHistory> list = localDB.QueryList<LogicHistory>(cmdstr);
            return list;
        }
        public void CreateLogicHistory()
        {
            string cmdstr = "CREATE TABLE [dbo].[历史逻辑报警表]([id][nvarchar](50) null,\r\n\t[传感器名称] [nvarchar](100) NULL,\r\n\t[时间] [datetime] NULL,\r\n\t[报警信息]  [nvarchar](max) NULL,\r\n\t[报警状态] [int] NULL,\r\n\t[备注] [nvarchar](max) NULL\r\n) ON [PRIMARY]\r\n";
            int i = localDB.SqlExecute(cmdstr);
        }

        public List<LogicHistory> GetLogicHistoryByTime(string tableDate)
        {
            List<LogicHistory> data = null;
            string cmdstr = "SELECT [id] as id,[传感器名称]\r\n as  Sensor ,[时间]\r\n  as Time,[报警信息]\r\n as AlarmMsg ,[报警状态]\r\n as Status ,[备注]\r\n as Describe  FROM [dbo].[历史逻辑报警表]\r\n   where 时间 >=  " + "'"+ tableDate+"'";
            data = localDB.QueryList<LogicHistory>(cmdstr);
            return data;
        }

        public int AddlogicHistory(LogicHistory logicHistory)
        {
            if (!IsExistTable("历史逻辑报警表"))
            {
                CreateLogicHistory();
            }
            string cmdstr = "INSERT INTO [dbo].[历史逻辑报警表]\r\n           ([id],[传感器名称]\r\n           ,[时间]\r\n           ,[报警信息]\r\n           ,[报警状态]\r\n           ,[备注])\r\n  VALUES ('" +logicHistory.Id+"','"+ logicHistory.Sensor + "','" + logicHistory.Time + "','" + logicHistory.AlarmMsg + "','" + logicHistory.Status + "','" + logicHistory.Describe + "')";
            int i =  localDB.SqlExecute(cmdstr);
            return i;
        }


        //public void CreateBrachStatusTable(string yymmdd)
        //{
        //    string cmdstr = "IF OBJECT_ID('分站状态记录表" + yymmdd + "','u') IS NULL BEGIN CREATE TABLE [dbo].[分站状态记录表" + yymmdd + "]( [分站地址] [smallint] NOT NULL, [时间] [datetime] NULL, [分站状态] [smallint] NULL, [数值] [float] NULL, [上传否] [bit] NULL, [上传否2] [bit] NULL, [上传否3] [bit] NULL, [上传否4] [bit] NULL) ON [PRIMARY] SELECT 1 END ELSE SELECT 0";
        //    db.SqlExecute(cmdstr);
        //}


        #endregion

        #region 人员卡号信息表
        public List<CardPerson> GetCarPersonList(bool refresh)
        { 
            List<CardPerson> list ;
            string cmdstr = "SELECT  [Card]     ,[Id]      ,[Enabled]      ,[Name]   FROM [tPerson]";
            if (refresh)
                LocalCache.Remove("CardPersonListKey");
            list = DWDB.QueryCacheList<CardPerson>("CardPersonListKey",cmdstr);
            return list;
        }
        #endregion
        #region 定位基站路径表
        public List<PositionPath> GetPositionPathList(int FromStationId=-1, int ToStationId = -1)
        {
            string cmdstr = "SELECT [起始基站编号] as FromStation\r\n      ,[结束基站编号] as ToStation\r\n      ,[路径编号] as SequenceId\r\n      ,[路径起点X] as StartX\r\n      ,[路径起点Y] as StartY\r\n      ,[路径终点X] as EndX\r\n      ,[路径终点Y] as EndY\r\n  FROM [dbo].[定位基站路径表] where 1=1";
            if(FromStationId > 0)
            {
                cmdstr += "  and 起始基站编号=" + FromStationId.ToString();
            }
            if (ToStationId > 0)
            {
                cmdstr += "  and 结束基站编号=" + ToStationId.ToString();
            }
            cmdstr += "  order by 起始基站编号,结束基站编号,路径编号 ";
            List<PositionPath> paths=localDB.QueryList<PositionPath>(cmdstr);
            return paths;
        }
        public int AddPositionPath(PositionPath path,int pathCount)
        {
            string cmdstr = "INSERT INTO [dbo].[定位基站路径表]\r\n           ([起始基站编号]\r\n           ,[结束基站编号]\r\n           ,[路径编号]\r\n           ,[路径起点X]\r\n           ,[路径起点Y]\r\n           ,[路径终点X]\r\n           ,[路径终点Y])\r\n     VALUES\r\n(";
            cmdstr += path.FromStation.ToString() + "," + path.ToStation.ToString() + " ," + path.SequenceId.ToString() + "," + path.StartX.ToString() + "," + path.StartY.ToString() + "," + path.EndX.ToString() + "," + path.EndY.ToString() + ")";
            int i=localDB.SqlExecute(cmdstr);
            cmdstr = "INSERT INTO [dbo].[定位基站路径表]\r\n           ([起始基站编号]\r\n           ,[结束基站编号]\r\n           ,[路径编号]\r\n           ,[路径起点X]\r\n           ,[路径起点Y]\r\n           ,[路径终点X]\r\n           ,[路径终点Y])\r\n     VALUES\r\n(";
            cmdstr += path.ToStation.ToString() + "," + path.FromStation.ToString() + " ," + pathCount.ToString() + "," + path.EndX.ToString() + "," + path.EndY.ToString() + "," + path.StartX.ToString() + "," + path.StartY.ToString() + ")";
            i = localDB.SqlExecute(cmdstr);
            return (i);
        }
        public void RemovePositionPath(int FromStation,int ToStation=-1)
        {
            string cmdstr = "delete from 定位基站路径表 where 起始基站编号="+FromStation.ToString();
            if (ToStation >= 0)
                cmdstr += " and 结束基站编号=" + ToStation.ToString();
            localDB.SqlExecute(cmdstr);

            cmdstr = "delete from 定位基站路径表 where 结束基站编号=" + FromStation.ToString();
            if (ToStation >= 0)
                cmdstr += " and 起始基站编号=" + ToStation.ToString();
            localDB.SqlExecute(cmdstr);
        }
        #endregion
        ///风电瓦斯参数表
        #region 控馈关系表
        public List<Control> GetControlList(string port)
        {
            string cmdstr = "SELECT cast(端口参数表.分站地址 as nvarchar(3))+'-'+ cast(端口参数表.端口序号 as nvarchar(3)) as RelatedSensor,[安装地点]\r\n  as InstallPlace    ,[传感器名称]\r\n   as RelatedSensorName,断电控制端口 as PortName,断电区域 as PortAreaName     FROM [dbo].[端口参数表] where 是否有效=1 and 是否已删除=0 and 是否断电控制=1 and replace(断电控制端口,' ','')!='' ";

            cmdstr+=" order by 分站地址,端口序号";
            //cmdstr += " order by 分站地址";
            List<Control> list = localDB.QueryList<Control>(cmdstr);
            List<Control> result=new List<Control> ();
            string[] ports, areas;
            int m;
            foreach (Control control in list)
            {
                ports = control.PortName.Replace(';',',').Split(',');
                if (string.IsNullOrEmpty(control.PortAreaName))
                    areas = ports;
                else
                    areas = control.PortAreaName.Replace(';', ',').Split(',');
                m=Math.Min(ports.Length, areas.Length);
                for (int i = 0; i < m; i++)
                {
                    var c = new Control();
                    if (!string.IsNullOrEmpty(ports[i]))
                    {
                        c.InstallPlace = control.InstallPlace;
                        c.RelatedSensor = control.RelatedSensor;
                        c.RelatedSensorName = control.RelatedSensorName;
                        c.PortName = ports[i];
                        c.PortAreaName = areas[i];
                    }
                    result.Add(c);
                }
            }
            if (!string.IsNullOrEmpty(port))
            {
                result = result.Where(p => p.PortName == port).ToList();
            }
            return result;
        }

        //+ "','" + immediate.MaxValue
        //public Control GetControlById(string BrachAdressId)
        //{
        //    Control data = null;
        //    string cmdstr = "select 分站编号 as StationId,风电瓦斯闭锁模式 as InMode,数值1 as Value1,数值2 as Value2,数值3 as Value3,数值4 as Value4,数值5 as Value5,数值6 as Value6 from 风电瓦斯配置参数表 where 分站编号=" + BrachAdressId;
        //    data = db.DataWorker<Control>(cmdstr);
        //    return data;
        //}

        //public int AddControl(Control Control)
        //{
        //    string cmdstr = "Insert into 风电瓦斯配置参数表 ([分站编号] ,[风电瓦斯闭锁模式],[数值1],[数值2],[数值3],[数值4],[数值5],[数值6])  VALUES  (" + interMode.StationId + ',' + interMode.InMode + ',' + interMode.Value1 + ',' + interMode.Value2 + ',' + interMode.Value3 + ',' + interMode.Value4 + ',' + interMode.Value5 + ',' + interMode.Value6 + ")";
        //    int i = db.SqlExecute(cmdstr);
        //    return i;
        //}
        //public void UpdateControl(Control Control)
        //{
        //    string cmd = " update 风电瓦斯配置参数表 ";
        //    cmd += " set 分站编号 = " + Control.StationId;
        //    cmd += ",风电瓦斯闭锁模式 = " + Control.InMode;
        //    cmd += ",数值1 = " + Control.Value1;
        //    cmd += ",数值2 = " + Control.Value2;
        //    cmd += ",数值3 = " + Control.Value3;
        //    cmd += ",数值4 = " + Control.Value4;
        //    cmd += ",数值5 = " + Control.Value5;
        //    cmd += ",数值6 = " + Control.Value6;
        //    cmd += "where 分站编号 = " + Control.StationId;
        //    db.SqlExecute(cmd);
        //}

        //public void CreateBrachStatusTable(string yymmdd)
        //{
        //    string cmdstr = "IF OBJECT_ID('分站状态记录表" + yymmdd + "','u') IS NULL BEGIN CREATE TABLE [dbo].[分站状态记录表" + yymmdd + "]( [分站地址] [smallint] NOT NULL, [时间] [datetime] NULL, [分站状态] [smallint] NULL, [数值] [float] NULL, [上传否] [bit] NULL, [上传否2] [bit] NULL, [上传否3] [bit] NULL, [上传否4] [bit] NULL) ON [PRIMARY] SELECT 1 END ELSE SELECT 0";
        //    db.SqlExecute(cmdstr);
        //}


        #endregion

        #region 井下轨迹
        public List<Track> GetTrackList(int cardId, DateTime From, DateTime To)
        {
            string year = From.ToString("yyyy");
            string cmdstr;
            if (PositionMode == "2")
            {
                cmdstr = "SELECT [recordid]\r\n      ,[RFID]  as CardId\r\n      ,[name] as PersonName\r\n      ,[type]\r\n      ,[fzAdd] as InId1 \r\n      ,[address]\r\n      ,[dt_arrive] as ArriveTime\r\n      ,[data1]\r\n      ,[data2]\r\n      ,[data3]\r\n      ,[data4]\r\n      ,[banci]\r\n      ,[remark]\r\n      ,[is_up]\r\n      ,[is_up2] FROM [dbo].[t_" + year + "] where 1=1";

                cmdstr += " and RFID=" + cardId + " and dt_arrive>='" + From.ToString("yyyy-MM-dd HH:mm:ss") + "' and dt_arrive<='" + To.ToString("yyyy-MM-dd HH:mm:ss") + "' order by dt_arrive ";
            }
            else
            {
                cmdstr = "SELECT [RecordId]\r\n   as RecordId   ,[Card] as CardId\r\n      ,[Name] as PersonName\r\n      ,[Type] as Type\r\n      ,[InId1]\r\n      ,[InId2]\r\n      ,[Address]\r\n      ,[AreaId]\r\n      ,[ArriveTime]\r\n      ,[Data1]\r\n      ,[Data2]\r\n      ,[Data3]\r\n      ,[Data4]\r\n      ,[Class]\r\n      ,[Remark]\r\n      ,[IsUp1]\r\n      ,[IsUp2]\r\n        FROM [dbo].[t" + year + "] where 1=1";

                cmdstr += " and card=" + cardId + " and ArriveTime>='" + From.ToString("yyyy-MM-dd HH:mm:ss") + "' and ArriveTime<='" + To.ToString("yyyy-MM-dd HH:mm:ss") + "' order by ArriveTime ";

            }
            List<Track> paths = DWDB.QueryList<Track>(cmdstr);
            return paths;
        }
        #endregion
        /// 
        #region 系统班次表
        public List<SystemShiftTable> GetSystemShiftTablesList()
        {
            string cmdstr = "SELECT [ID]\r\n as ID,[startTime]\r\n as StartTime ,[endTime]\r\n as EndTime  FROM [dbo].[系统班次表] ";
            List<SystemShiftTable> list = localDB.QueryList<SystemShiftTable>(cmdstr);
            return list;
        }

        public int  UpdateSystemShiftTable(SystemShiftTable systemShiftTable)
        {
            string cmdstr;
            int i;
            if (SelectSystemShiftTableByID(systemShiftTable.ID) != null)
            {
                cmdstr = "UPDATE [dbo].[系统班次表]  SET [startTime] = '" + systemShiftTable.StartTime + "',[endTime] = '" + systemShiftTable.EndTime + "' WHERE ID = " + systemShiftTable.ID;
                i = localDB.SqlExecute(cmdstr);
            }
            else
                i = AddSystemShiftTable(systemShiftTable);
            return i;
        }


        public int AddSystemShiftTable(SystemShiftTable systemShiftTable)
        {
            string cmdstr = "insert into  [系统班次表] ([ID],[startTime],[endTime])  VALUES ('" +  systemShiftTable.ID + "','" + systemShiftTable.StartTime.ToString("yyyy-MM-dd HH:mm:ss") + "','" + systemShiftTable.EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "')";
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }

        public int  DeleteSystemShiftTable(string  Id)
        {
            string cmdstr = "DELETE FROM [dbo].[系统班次表]\r\n   WHERE ID = " + Id ;
            int i = localDB.SqlExecute(cmdstr);
            return i;
        }

        public SystemShiftTable SelectSystemShiftTableByID(int id)
        {
            string cmdstr = "SELECT [ID]\r\n as ID,[startTime]\r\n as StartTime ,[endTime]\r\n as EndTime  FROM [dbo].[系统班次表] WHERE ID = " + id;
            SystemShiftTable sst = localDB.DataWorker<SystemShiftTable>(cmdstr);
            return sst;
        }

        //+ "','" + immediate.MaxValue
        //public Control GetControlById(string BrachAdressId)
        //{
        //    Control data = null;
        //    string cmdstr = "select 分站编号 as StationId,风电瓦斯闭锁模式 as InMode,数值1 as Value1,数值2 as Value2,数值3 as Value3,数值4 as Value4,数值5 as Value5,数值6 as Value6 from 风电瓦斯配置参数表 where 分站编号=" + BrachAdressId;
        //    data = db.DataWorker<Control>(cmdstr);
        //    return data;
        //}

        //public int AddControl(Control Control)
        //{
        //    string cmdstr = "Insert into 风电瓦斯配置参数表 ([分站编号] ,[风电瓦斯闭锁模式],[数值1],[数值2],[数值3],[数值4],[数值5],[数值6])  VALUES  (" + interMode.StationId + ',' + interMode.InMode + ',' + interMode.Value1 + ',' + interMode.Value2 + ',' + interMode.Value3 + ',' + interMode.Value4 + ',' + interMode.Value5 + ',' + interMode.Value6 + ")";
        //    int i = db.SqlExecute(cmdstr);
        //    return i;
        //}

        //public void UpdateControl(Control Control)
        //{
        //    string cmd = " update 风电瓦斯配置参数表 ";
        //    cmd += " set 分站编号 = " + Control.StationId;
        //    cmd += ",风电瓦斯闭锁模式 = " + Control.InMode;
        //    cmd += ",数值1 = " + Control.Value1;
        //    cmd += ",数值2 = " + Control.Value2;
        //    cmd += ",数值3 = " + Control.Value3;
        //    cmd += ",数值4 = " + Control.Value4;
        //    cmd += ",数值5 = " + Control.Value5;
        //    cmd += ",数值6 = " + Control.Value6;
        //    cmd += "where 分站编号 = " + Control.StationId;
        //    db.SqlExecute(cmd);
        //}

        //public void CreateBrachStatusTable(string yymmdd)
        //{
        //    string cmdstr = "IF OBJECT_ID('分站状态记录表" + yymmdd + "','u') IS NULL BEGIN CREATE TABLE [dbo].[分站状态记录表" + yymmdd + "]( [分站地址] [smallint] NOT NULL, [时间] [datetime] NULL, [分站状态] [smallint] NULL, [数值] [float] NULL, [上传否] [bit] NULL, [上传否2] [bit] NULL, [上传否3] [bit] NULL, [上传否4] [bit] NULL) ON [PRIMARY] SELECT 1 END ELSE SELECT 0";
        //    db.SqlExecute(cmdstr);
        //}


        #endregion

        #region 充放电记录表
        public int AddChargeRecord(int stationId, DateTime time, int  status,string mode)
        {
            string cmd = "insert into 充放电记录表( 分站号,充放电时间,状态,模式) values(" + stationId + ",'" + (time == null ? "null" : time) + "'," + status + ",'"+mode+"')";
            int i = localDB.SqlExecute(cmd);
            return i;
        }
        public int UpdateChargeRecord(int stationId, DateTime time)
        {
            string cmd = "update  充放电记录表 set  完成时间='" + (time == null ? "null" : time) + "', 耗时=DATEDIFF(MINUTE,充放电时间,'" + time.ToString("yyyy-MM-dd HH:mm:ss") + "') where 分站号="+stationId+" and 完成时间 is NULL and 状态=1";
            int i = localDB.SqlExecute(cmd);
            return i;
        }
        public List<ChargeRecord> GetChargeRecords(int? stationId, DateTime? startEateTime, DateTime? endDateTime, int? status)
        {
            string cmd = "select id, 分站号 as StationId,充放电时间 as ChargeDate, 状态 as Status, 完成时间 as EndDate, 耗时 as Duration ,模式 as Mode from 充放电记录表 where 1=1 ";
            if (stationId != null)
                cmd += " and 分站号="+stationId+ " ";
            if(startEateTime!=null)
                cmd += " and 充放电时间>='" + startEateTime + "' ";
            if (endDateTime != null)
                cmd += " and 充放电时间<='" + endDateTime + "' ";
            if (status != null)
                cmd += " and 状态=" + status + " ";
            cmd += "  order by  充放电时间 DESC";
            List<ChargeRecord> data= localDB.QueryList<ChargeRecord>(cmd);
            return data;
        }
        #endregion
        #region 测点位置表

        public int addSensorLocation(int sensorId,string sensorName, double x, double y, int type)
        {
            string cmd = "insert into 测点位置表 ( 系统编号,显示名称,横坐标,纵坐标,类型) values(" + sensorId + ",'" +sensorName+ "'," + x + ","+y+","+type+")";
            int i = localDB.SqlExecute(cmd);
            return i;
        }
        public List<SensorLocation> GetSensorLocations()
        {
            string cmd = "select id, 系统编号 as SensorId,显示名称 as SensorName, 横坐标 as x, 纵坐标 as y,类型 as type,下井口横坐标 as DownX, 下井口纵坐标 as DownY,上井口横坐标 as UpX,上井口纵坐标 as UpY ,上井口距离 as UpDistance,下井口距离 as DownDistance  from 测点位置表 where 1=1 ";

            List<SensorLocation> data = localDB.QueryList<SensorLocation>(cmd);
            return data;
        }
        /// <summary>
        /// 获取与自己没有路径的基站列表
        /// </summary>
        /// <returns></returns>
        public List<SensorLocation> GetUnRelatedSensorLocations(int id)
        {
            string cmd = "select id, 系统编号 as SensorId,显示名称 as SensorName, 横坐标 as x, 纵坐标 as y,类型 as type,下井口横坐标 as DownX, 下井口纵坐标 as DownY,上井口横坐标 as UpX,上井口纵坐标 as UpY ,上井口距离 as UpDistance,下井口距离 as DownDistance from 测点位置表\r\nwhere 1=1 and 系统编号 not in (select 结束基站编号 from 定位基站路径表 where 起始基站编号=" + id+") and 类型=2 and 系统编号<>" + id.ToString();

            List<SensorLocation> data = localDB.QueryList<SensorLocation>(cmd);
            return data;
        }
        public SensorLocation GetSensorLocation(int sensorId,int type)
        {
            string cmd = "select id, 系统编号 as SensorId,显示名称 as SensorName, 横坐标 as x, 纵坐标 as y,类型 as type,下井口横坐标 as DownX, 下井口纵坐标 as DownY,上井口横坐标 as UpX,上井口纵坐标 as UpY,上井口距离 as UpDistance,下井口距离 as DownDistance from 测点位置表 where 系统编号= " + sensorId+" and 类型="+type;
            SensorLocation sensorLocation = localDB.GetSingle<SensorLocation>(cmd);
            return sensorLocation;
        }
        public int DelSensorLocation(int sensorId,int type)
        {
            string cmd = "delete from 测点位置表 where  系统编号=" + sensorId + " and 类型="+type;
            int i = localDB.SqlExecute(cmd);
            if (type == 2)
            {
                cmd = "delete from 定位基站路径表 where  起始基站编号=" + sensorId + " or 结束基站编号=" + sensorId;
                i = localDB.SqlExecute(cmd);

            }
            return i;
        }
        public void UpdateSensorLocationName(string id,string name)
        {
            string cmd = "update 测点位置表 set 显示名称='"+name+"' where 系统编号="+id;
            localDB.SqlExecute(cmd);
        }
        public int UpdateSensorExitLocation(int sensorId, int type,double x, double y,int distance)
        {
            string cmd;
            if (type == 0)
            {
                if (!double.IsNaN(x) && !double.IsNaN(y))
                    cmd = "update 测点位置表 set   下井口横坐标=" + x + ", 下井口纵坐标=" + y + ",上井口距离=" + distance + "  where  系统编号=" + sensorId + " and 类型=2";
                else
                    cmd = "update 测点位置表 set   下井口横坐标=NULL, 下井口纵坐标=NULL,上井口距离=NULL  where  系统编号=" + sensorId + " and 类型=2";
            }
            else if (type == 1)
            {
                cmd = "update 测点位置表 set   上井口横坐标=" + (double.IsNaN(x) ? "NULL" : x) + ", 上井口纵坐标=" + (double.IsNaN(y) ? "NULL" : y) + ",下井口距离=" + distance + " where  系统编号=" + sensorId + " and 类型=2";
            }
            else
                cmd = "update 测点位置表 set   下井口横坐标=" + x + ", 下井口纵坐标=" + y + ", 上井口横坐标=" + x + ", 上井口纵坐标=" + y + " where  系统编号=" + sensorId + " and 类型=2";
            int i = localDB.SqlExecute(cmd);
            return i;
        }
        public int UpdateSensorLocation(string sensorId, int locationType,double x,double y)
        {
            string cmd;
            cmd = "update 测点位置表 set   横坐标=" + x + ", 纵坐标=" + y + "  where 系统编号="+ sensorId+" and 类型="+ locationType;
            int i = localDB.SqlExecute(cmd);
            cmd = "delete from 定位基站路径表 where 起始基站编号=" + sensorId + " or 结束基站编号=" + sensorId;
            i = localDB.SqlExecute(cmd);

            return i;
        }
        #endregion
        #region 三维模型表

        public int addModel(int sensorId, string sensorName, double x, double y, double z,int type)
        {
            string cmd = "insert into 三维模型位置表 ( 系统编号,显示名称,x坐标,y坐标,z坐标,类型) values(" + sensorId + ",'" + sensorName + "'," + x + "," + y + "," +z+','+ type + ")";
            int i = localDB.SqlExecute(cmd);
            return i;
        }
        public List<Model3d> GetModel()
        {
            string cmd = "select id, 系统编号 as SensorId,显示名称 as SensorName, x坐标 as x, y坐标 as y,z坐标 as z,类型 as type from 三维模型表 where 1=1 ";

            List<Model3d> data = localDB.QueryList<Model3d>(cmd);
            return data;
        }
        
        public int DelMode(int sensorId, int type)
        {
            string cmd = "delete from 三维模型表 where  系统编号=" + sensorId + " and 类型=" + type;
            int i = localDB.SqlExecute(cmd);

            return i;
        }
        public void UpdateModelName(string id, string name)
        {
            string cmd = "update 三维模型表 set 显示名称='" + name + "' where 系统编号=" + id;
            localDB.SqlExecute(cmd);
        }
       
        #endregion
        #region "表初始化"
        private bool IsExistField(string tableName, string fieldName)
        {
            bool exist = false;
            string sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tableName + "' AND COLUMN_NAME = '"+ fieldName + "'";
            List<object> v=localDB.QueryList<object>(sql);
            if (v != null&&v.Count>0)
                exist = true;
            return exist;
        }
        private void AddNewField(string tableName, string fieldName, string type, int len=0)
        {
            string sql = "ALTER TABLE [" + tableName + "] ADD [" + fieldName + "] [" + type + "] ";
            if(len>0)
                sql+=" ("+len+") null";
            localDB.SqDDLlExecute(sql);
        }
        public void CheckDB()
        {
            if (!IsExistTable("历史数据表" + DateTime.Now.ToString("yyMMdd")))
            {
                CreateHistoryDataTable(DateTime.Now.ToString("yyMMdd"));
            }
            if (!IsExistTable("开关数据表" + DateTime.Now.ToString("yyMMdd")))
            {
                CreateSwtichRecordTable(DateTime.Now.ToString("yyMMdd"));
            }
            if (!IsExistTable("报警数据表" + DateTime.Now.ToString("yyMMdd")))
            {
                CreateAlertTable(DateTime.Now.ToString("yyMMdd"));
            }
            if (!IsExistTable("历史逻辑报警表"))
            {
                CreateLogicHistory();
            }
            if (!IsExistField("历史数据表"+DateTime.Now.ToString("yyMMdd"), "数据状态"))
                AddNewField("历史数据表" + DateTime.Now.ToString("yyMMdd"), "数据状态", "int");
            if (!IsExistField("历史数据表" + DateTime.Now.ToString("yyMMdd"), "时间2"))
                AddNewField("历史数据表" + DateTime.Now.ToString("yyMMdd"), "时间2", "Datetime");
            if (!IsExistField("历史数据表" + DateTime.Now.ToString("yyMMdd"), "原因"))
                AddNewField("历史数据表" + DateTime.Now.ToString("yyMMdd"), "原因", "nvarchar", 50);
            if (!IsExistField("历史数据表" + DateTime.Now.ToString("yyMMdd"), "措施"))
                AddNewField("历史数据表" + DateTime.Now.ToString("yyMMdd"), "措施", "nvarchar", 50);
            if (!IsExistField("开关数据表" + DateTime.Now.ToString("yyMMdd"), "时间2"))
                AddNewField("开关数据表" + DateTime.Now.ToString("yyMMdd"), "时间2", "Datetime");
            if (!IsExistField("报警数据表" + DateTime.Now.ToString("yyMMdd"), "校准浓度"))
                AddNewField("报警数据表" + DateTime.Now.ToString("yyMMdd"), "校准浓度", "float");
            if (!IsExistField("即时数据表", "报警开始时间"))
                AddNewField("即时数据表", "报警开始时间", "Datetime");
            if (!IsExistField("即时数据表", "断电开始时间"))
                AddNewField("即时数据表", "断电开始时间", "Datetime");
            if (!IsExistField("即时数据表", "断线开始时间"))
                AddNewField("即时数据表", "断线开始时间", "Datetime");
            if (!IsExistField("即时数据表", "故障开始时间"))
                AddNewField("即时数据表", "故障开始时间", "Datetime");
            if (!IsExistField("即时数据表", "馈电异常开始时间"))
                AddNewField("即时数据表", "馈电异常开始时间", "Datetime");
            if (!IsExistField("即时数据表", "最大值时刻"))
                AddNewField("即时数据表", "最大值时刻", "Datetime");

            if (!IsExistField("即时数据表", "报警否"))
                AddNewField("即时数据表", "报警否", "int");
            if (!IsExistField("即时数据表", "断电否"))
                AddNewField("即时数据表", "断电否", "int");
            if (!IsExistField("即时数据表", "断线否"))
                AddNewField("即时数据表", "断线否", "int");
            if (!IsExistField("即时数据表", "故障否"))
                AddNewField("即时数据表", "故障否", "int");
            if (!IsExistField("即时数据表", "馈电异常否"))
                AddNewField("即时数据表", "馈电异常否", "int");
            if (!IsExistField("即时数据表", "安全措施"))
                AddNewField("即时数据表", "安全措施", "nvarchar", 50);

            if (!IsExistField("端口参数表", "关联传感器"))
                AddNewField("端口参数表", "关联传感器", "nvarchar",50);
            if (!IsExistField("端口参数表", "摄像头IP"))
                AddNewField("端口参数表", "摄像头IP", "nvarchar", 50);
            if (!IsExistField("端口参数表", "摄像头用户名"))
                AddNewField("端口参数表", "摄像头用户名", "nvarchar", 50);
            if (!IsExistField("端口参数表", "摄像头密码"))
                AddNewField("端口参数表", "摄像头密码", "nvarchar", 50);
            if (!IsExistField("历史逻辑报警表", "id"))
                AddNewField("历史逻辑报警表", "id", "nvarchar", 50);

            if (!IsExistField("站点配置表", "判定断线时间"))
                AddNewField("站点配置表", "判定断线时间", "int");
            if (!IsExistField("站点配置表", "开停传感器"))
                AddNewField("站点配置表", "开停传感器", "nvarchar", 255);
            if (!IsExistField("站点配置表", "充放电周期"))
                AddNewField("站点配置表", "充放电周期", "int");
            if (!IsExistField("站点配置表", "上次充放电时间"))
                AddNewField("站点配置表", "上次充放电时间", "datetime");
            if (!IsExistField("测点位置表", "下井口距离"))
                AddNewField("测点位置表", "下井口距离", "int");
            if (!IsExistField("测点位置表", "上井口距离"))
                AddNewField("测点位置表", "上井口距离", "int");
            if (!IsExistField("区域信息配置表", "id"))
                AddNewField("区域信息配置表", "id", "nvarchar", 50);
            if (!IsExistField("区域信息配置表", "区域文件名称"))
                AddNewField("区域信息配置表", "区域文件名称", "nvarchar", 50);
            if (!IsExistTable("测点位置表"))
            {
                string str = "CREATE TABLE [dbo].[测点位置表](\r\n\t[id] [int] IDENTITY(1,1) NOT NULL,\r\n\t[系统编号] [int] NULL,\r\n\t[显示名称] [nvarchar](50) NULL,\r\n\t[横坐标] [int] NULL,\r\n\t[纵坐标] [int] NULL,\r\n\t[类型] [int] NULL,\r\n\t[下井口横坐标] [int] NULL,\r\n\t[下井口纵坐标] [int] NULL,\r\n\t[上井口横坐标] [int] NULL,\r\n\t[上井口纵坐标] [int] NULL,\r\n\t[上井口距离] [int] NULL,\r\n\t[下井口纵距离] [int] NULL,\r\n\t[下井口距离] [int] NULL,\r\n\t[校准点横坐标] [int] NULL,\r\n\t[校准点纵坐标] [int] NULL,\r\n\t[横偏差] [int] NULL,\r\n\t[纵偏差] [int] NULL\r\n) ON [PRIMARY]";
                localDB.SqDDLlExecute(str);
            }
            if (!IsExistTable("充放电记录表"))
            {
                string str = "CREATE TABLE [dbo].[充放电记录表](\r\n\t[id] [int] IDENTITY(1,1) NOT NULL,\r\n\t[分站号] [int] NULL,\r\n\t[充放电时间] [datetime] NULL,\r\n\t[状态] [int] NULL,\r\n\t[完成时间] [datetime] NULL,\r\n\t[耗时] [float] NULL\r\n, [模式] [nvarchar](5) NULL) ON [PRIMARY]";
                localDB.SqDDLlExecute(str);
            }
            if (!IsExistTable("系统班次表"))
            {
                string str = "CREATE TABLE [dbo].[系统班次表](\r\n\t[ID] [int] IDENTITY(1,1) NOT NULL,\r\n\t[startTime] [datetime] NULL,\r\n\t[endTime] [datetime] NULL,\r\nPRIMARY KEY CLUSTERED \r\n(\r\n\t[ID] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n) ON [PRIMARY]";
                localDB.SqDDLlExecute(str);
            }
            if (!IsExistTable("定位基站路径表"))
            {
                string str = "CREATE TABLE [dbo].[定位基站路径表](\r\n\t[起始基站编号] [int] NOT NULL,\r\n\t[结束基站编号] [int] NOT NULL,\r\n\t[路径编号] [int] NULL,\r\n\t[路径起点X] [int] NULL,\r\n\t[路径起点Y] [int] NULL,\r\n\t[路径终点X] [int] NULL,\r\n\t[路径终点Y] [int] NULL\r\n) ON [PRIMARY]";
                localDB.SqDDLlExecute(str);
            }
            if (!IsExistTable("三维模型位置表"))
            {
                string str = "CREATE TABLE [dbo].[三维模型位置表](\r\n\t[id] [int] IDENTITY(1,1) NOT NULL,\r\n\t[系统编号] [int] NOT NULL,\r\n\t[显示名称] [varchar](50) NULL,\r\n\t[类型] [int] NOT NULL,\r\n\t[x坐标] [float] NOT NULL,\r\n\t[y坐标] [float] NOT NULL,\r\n\t[z坐标] [float] NOT NULL\r\n) ON [PRIMARY]";
                localDB.SqDDLlExecute(str);
            }
            if (!IsExistTable("传感器字典"))
            {
                string str = "CREATE TABLE [dbo].[传感器字典](\r\n\t[id] [int] IDENTITY(1,1) NOT NULL,\r\n\t[传感器类型] [nvarchar](50) NULL,\r\n\t[传感器代码] [int] NULL,\r\n\t[是否模拟量] [int] NULL,\r\n\t[传感器名称] [nvarchar](50) NULL,\r\n\t[单位] [nvarchar](50) NULL,\r\n\t[量程上限] [int] NULL,\r\n\t[量程下限] [int] NULL,\r\n\t[计算方法] [int] NULL,\r\n\t[是否负数] [int] NULL,\r\n\t[小数位数] [int] NULL,\r\n \t[基值] [int] NULL,\r\n\t[系数] [float] NULL,\r\n\t[开显示] [nvarchar](50) NULL,\r\n\t[停显示] [nvarchar](50) NULL CONSTRAINT [PK_传感器字典] PRIMARY KEY CLUSTERED \r\n(\r\n\t[id] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n) ON [PRIMARY]";
                localDB.SqDDLlExecute(str);
            }

            if (!IsExistField("传感器字典", "基值"))
                AddNewField("传感器字典", "基值", "int"); 
            if (!IsExistField("传感器字典", "系数"))
                AddNewField("传感器字典", "系数", "float");
            if (!IsExistField("传感器字典", "开显示"))
                AddNewField("传感器字典", "开显示", "nvarchar", 50);
            if (!IsExistField("传感器字典", "停显示"))
                AddNewField("传感器字典", "停显示", "nvarchar", 50);
            string str1 = "update 站点配置表 set 开停传感器=';;;;;;;' where 开停传感器 is null";
            localDB.SqDDLlExecute(str1);
            str1 = "ALTER TABLE 用户权限 ALTER COLUMN UserUnit nvarchar(50);ALTER TABLE 用户权限 ALTER COLUMN 用户名 nvarchar(50);ALTER TABLE 用户权限 ALTER COLUMN 显示名称 nvarchar(50);ALTER TABLE 用户权限 ALTER COLUMN 密码 nvarchar(50);ALTER TABLE 用户权限 ALTER COLUMN 权限字串 nvarchar(50);";
            localDB.SqDDLlExecute(str1);
        }
        public void CreateStoredProcedure()
        {
            
            string str = "CREATE PROCEDURE [dbo].[GenerateChartsData]\r\n@StartDate DATETIME,\r\n@EndDate DATETIME,\r\n@SensorID varchar(50),\r\n@granularity int,\r\n@sensorType int --0开关量，1模拟量\r\nAS\r\nBEGIN\r\nSET NOCOUNT ON;\r\n-- 创建临时表来存储结果\r\nCREATE TABLE #HourlyData ([时间] DATETIME, [当前值] float);\r\nCREATE TABLE #tmp ([时间] DATETIME, [当前值] float);\r\n-- 循环插入两个日期之间的每个小时的数据\r\nDECLARE @CurrentDate DATETIME = DATEADD(HOUR, DATEDIFF(HOUR, 0, @StartDate), 0); -- 取@StartDate的整点时间\r\nDECLARE @StartTime DATETIME ;\r\nDECLARE @CurseTime DATETIME;\r\nDECLARE @DateStr varchar(50);\r\nIF @granularity=13 BEGIN\r\n\t--按小时补齐\r\n\tSET @CurseTime =DATEADD(HOUR,-1,CAST(LEFT(CONVERT(VARCHAR(50),@CurrentDate,120), @granularity)+':00:00' AS datetime));\r\n\tSET @DateStr = ':00:00';\r\nEND\r\nELSE IF @granularity=15 BEGIN\r\n\t--按10分钟补齐\r\n\tSET @CurseTime =DATEADD(MINUTE,-10,CAST(LEFT(CONVERT(VARCHAR(50),@CurrentDate,120), @granularity)+'0:00' AS datetime));\r\n\tSET @DateStr = '0:00';\r\nEND\r\nIF @granularity=16 BEGIN\r\n\t\t   --按1分钟补齐\r\n\tSET @CurseTime =DATEADD(MINUTE,-1,CAST(LEFT(CONVERT(VARCHAR(50),@CurrentDate,120), @granularity)+':00' AS datetime));\r\n\tSET @DateStr = ':00';\r\nEND\r\nIF @granularity=19 BEGIN\r\n   --按秒补齐\r\n   SET @CurseTime = DATEADD(SECOND, -1, @CurrentDate);\r\n   SET @DateStr = '';\r\nEND\r\n -- 生成每个小时的时间范围\r\nDECLARE @TableName NVARCHAR(100) = N'历史数据表' + RIGHT(CONVERT(VARCHAR(8), @CurrentDate, 112), 6); -- 构建表名\r\n  -- 构建动态 SQL 查询语句,生成第一条数据\r\nDECLARE @Sql NVARCHAR(MAX);\r\n\r\nset @StartTime =@StartDate;\r\nSET @TableName = N'历史数据表' + RIGHT(CONVERT(VARCHAR(8), @CurrentDate, 112), 6); -- 构建表名\r\nIF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =  @TableName AND COLUMN_NAME = '数据状态')\r\nBEGIN\r\n\tset @Sql= N'ALTER TABLE '+@TableName+' ADD [数据状态] [VARCHAR] (50);'\r\n\tEXEC sp_executesql @Sql\r\nEND\r\nset @Sql= N'\r\n\t\t\tINSERT INTO #tmp ([时间], [当前值])\r\n\t\t\tSELECT top 1  LEFT(CONVERT(VARCHAR(50),[时间],120) , @granularity) +  '''+@DateStr+''' AS C , [当前值]\r\n\t\t\tFROM [dbo].' + QUOTENAME(@TableName) + '\r\n\t\t\tWHERE 时间 <= @StartTime AND 系统序号 ='+ @SensorID +'  and (数据状态!=''-1'' or 数据状态 is null) ORDER BY [时间] DESC;';\r\n-- 执行动态 SQL 查询语句\r\nEXEC sp_executesql @Sql, N'@granularity INT,@CurseTime DATETIME,@StartTime DATETIME',@granularity,@CurseTime,@StartDate;\r\n\r\n--插入历史数据表中存在的数据\r\nWHILE @CurrentDate <= @EndDate\r\nBEGIN\r\n\tSET @TableName = N'历史数据表' + RIGHT(CONVERT(VARCHAR(8), @CurrentDate, 112), 6); -- 构建表名\r\n\tIF EXISTS (SELECT * FROM sys.tables WHERE name = @TableName)\r\n\tBEGIN\r\n\t\tIF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =  @TableName AND COLUMN_NAME = '数据状态')\r\n\t\tBEGIN\r\n\t\t\tset @Sql= N'ALTER TABLE '+@TableName+' ADD [数据状态] [VARCHAR] (50);'\r\n\t\t\tEXEC sp_executesql @Sql\r\n\t\tEND\r\n\t\t--按照时间粒度插入最大值\r\n\t\tset @Sql= N'\r\n\t\t\t\t\tINSERT INTO #HourlyData ([时间], [当前值])\r\n\t\t\t\t\tSELECT C+'''+@DateStr+''',MAX(M) FROM (\r\n\t\t\t\t\tSELECT LEFT(CONVERT(VARCHAR(50),[时间],120) , @granularity)  AS C, [当前值] AS M \r\n\t\t\t\t\tFROM [dbo].' + QUOTENAME(@TableName) + ' \r\n\t\t\t\t\tWHERE 系统序号 ='+ @SensorID +' AND 时间 >= @CurrentDate AND 时间 <= @EndDate and (数据状态!=''-1'' or 数据状态 is null)) T GROUP BY C'; \r\n\t\t\t\t\t\r\n\t\tEXEC sp_executesql @Sql, N'@granularity int, @CurrentDate DATETIME, @EndDate DATETIME', @granularity, @CurrentDate, @EndDate; \r\n\t\t--按照时间粒度插入最后一条数据值\r\n\t\tset @Sql= N'\r\n\t\t\t\t\tINSERT INTO #tmp ([时间], [当前值])\r\n\t\t\t\t\tselect B.C+'''+@DateStr+''',A.当前值 from [dbo].' + QUOTENAME(@TableName) + ' A  join (\r\n\t\t\t\t\tSELECT C, MAX(s) as minv,id FROM (\r\n\t\t\t\t\tSELECT  LEFT(CONVERT(VARCHAR(50),[时间],120) ,  @granularity)  AS C, [当前值] AS M ,时间 as S, [系统序号] as ID\r\n\t\t\t\t\tFROM [dbo].' + QUOTENAME(@TableName) + '\r\n\t\t\t\t\tWHERE 系统序号 ='+ @SensorID +' AND 时间 >= @CurrentDate AND 时间 <= @EndDate   and (数据状态!=''-1'' or 数据状态 is null)) T GROUP BY C,id) B ON A.[时间]=b.minv  and A.系统序号 =b.id'; \r\n\t\t\t\t\t\r\n\t\tEXEC sp_executesql @Sql, N'@granularity int, @CurrentDate DATETIME, @EndDate DATETIME', @granularity, @CurrentDate, @EndDate;\r\n\tEND \r\n\tSET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);\r\n\r\nEND\r\nSET @CurrentDate  = DATEADD(HOUR, DATEDIFF(HOUR, 0, @StartDate), 0);\r\n\t--SELECT [时间], [当前值] FROM #TMP order by [时间];\r\nWHILE @CurrentDate <= @EndDate\r\n\tBEGIN\r\n\t\tSET @TableName = N'历史数据表' + RIGHT(CONVERT(VARCHAR(8), @CurrentDate, 112), 6); -- 构建表名\r\n\r\n\t\t-- 检查表是否存在\r\n\t\tIF EXISTS (SELECT * FROM sys.tables WHERE name = @TableName)\r\n\t\tBEGIN\r\n\t\t\t-- 检查临时表是否为空\r\n\t\t\tIF NOT EXISTS (SELECT * FROM #HourlyData WHERE  LEFT(CONVERT(VARCHAR(50),[时间],120), @granularity)= LEFT(CONVERT(VARCHAR(50),@CurrentDate,120), @granularity))\r\n\t\t\tBEGIN\r\n\t\t\t\t-- 获取上一个时间节点的值\r\n\t\t\t\tDECLARE @PreviousValue FLOAT;\r\n\t\t\t\tSELECT TOP 1 @PreviousValue = [当前值] FROM #tmp WHERE [时间] <= @CurrentDate  ORDER BY [时间] DESC;\r\n\t\t\t\t-- 插入新数据\r\n\t\t\t\tINSERT INTO #HourlyData ([时间], [当前值]) VALUES (@CurrentDate, @PreviousValue);\r\n\t\t\tEND\r\n\t\tEND\r\n\t\t--ELSE\r\n\t\t--BEGIN\r\n\t\t--\t-- 获取上一个时间节点的值\r\n\t\t--\tDECLARE @PreviousValue2 INT;\r\n\t\t--\tSELECT TOP 1 @PreviousValue2 = [当前值] FROM #HourlyData ORDER BY [时间] DESC;\r\n\r\n\t\t--\t-- 插入新数据\r\n\t\t--\tINSERT INTO #HourlyData ([时间], [当前值]) VALUES (@CurrentDate, @PreviousValue2);\r\n\t\t--END\r\n\r\n\tSET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)\r\n\r\nEND;\r\n\t--SELECT [时间], [当前值] FROM #HourlyData order by [时间];\r\n\t-- 补齐时间范围内缺失的数据\r\nDECLARE @MissingTime DATETIME = @StartDate;\r\nWHILE @MissingTime <= @EndDate\r\nBEGIN\r\n    -- 检查是否已存在数据\r\n    DECLARE @ExistingData FLOAT;\r\n    SELECT @ExistingData = COUNT(*) FROM #HourlyData WHERE [时间] = @MissingTime;\r\n    \r\n    IF @ExistingData = 0\r\n    BEGIN\r\n        -- 获取上一条数据的有效值\r\n        DECLARE @PreviousValue3 FLOAT;\r\n        SELECT TOP 1 @PreviousValue3 = [当前值] FROM #TMP WHERE [时间] <= @MissingTime ORDER BY [时间] DESC;\r\n        \r\n        -- 插入新的数据\r\n        INSERT INTO #HourlyData ([时间], [当前值]) VALUES (@MissingTime, @PreviousValue3);\r\n    END\r\n    \r\n    -- 根据时间粒度增加时间\r\n    IF @granularity = 13\r\n        SET @MissingTime = DATEADD(HOUR, 1, @MissingTime);\r\n    ELSE IF @granularity = 15\r\n        SET @MissingTime = DATEADD(MINUTE, 10, @MissingTime);\r\n    ELSE IF @granularity = 16\r\n        SET @MissingTime = DATEADD(MINUTE, 1, @MissingTime);\r\n    ELSE IF @granularity = 19\r\n        SET @MissingTime = DATEADD(SECOND, 1, @MissingTime);\r\nEND\r\n\r\n\t-- 返回结果\r\n\tIF @sensorType=0\r\n\tBEGIN\r\n\t\tUPDATE #HourlyData SET 当前值=1 WHERE 当前值>0;\r\n\tEND\r\n\tSELECT @sensorId as SensorId,当前值 as CurValue,时间 as Time FROM #HourlyData order by [时间];\r\nEND;;";
            localDB.SqDDLlExecute(str);
        }
        public void DeleteStoredProcedure()
        {
            string str = "DROP  PROCEDURE [dbo].[GenerateChartsData] ";
            localDB.SqDDLlExecute(str);
        }
        public void AddWindGasLock(WindGasLock windGasLock)
        {
            string cmd = "insert into WindPowerGasLock" + DateTime.Now.ToString("yyyy") + " values ('"+windGasLock.Message+"','"+windGasLock.StartTime.ToString("yyyy-MM-dd HH:mm:ss")+"','"+windGasLock.EndTime.ToString("yyyy-dd-MM HH:mm:ss")+"','"+windGasLock.SaveTime.ToString("yyyy-dd-MM HH:mm:ss")+"',"+windGasLock.Type+","+windGasLock.ProcessType+",'"+windGasLock.Remark+"')";
            localDB.SqlExecute(cmd);
        }
        public List<WindGasLock> GetAllWindGasLock(string year) { 
            List<WindGasLock> list = null;
            string cmd = "select * from WindPowerGasLock"+year;
            list=localDB.QueryList<WindGasLock>(cmd);
            return list;
        }

        public void Dispose()
        {
            localDB.Dispose();
            remoteDB.Dispose();
            DWDB.Dispose();
            GBDB.Dispose();
        }
        #endregion
        private static string Md5Hash(string input)
        {
            MD5CryptoServiceProvider md5Hasher = new MD5CryptoServiceProvider();
            byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(input));
            StringBuilder sBuilder = new StringBuilder();
            for (int i = 0; i < data.Length; i++)
            {
                sBuilder.Append(data[i].ToString("x2"));
            }
            return sBuilder.ToString();
        }
    }

}
